Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.

I am trying to unload a DB into files using DBUNLOAD, and then load/input the resulted tables(.dat files) into an empty DB. I have problem in inputing these tables because of not respecting the order of relationships between tables. Example: a table containing a foreign key is trying to be loaded before the corresponding table with primary key being loaded!! How should I tell the DBUNLOAD to respect the relationships of tables? I didnt find any such option in the documentation. As a workaround I have used SET OPTION WAIT_FOR_COMMIT='ON', but this makes the load process much more slower. Anyone could help?

asked 19 Sep '18, 11:48

Baron's gravatar image

accept rate: 48%

> As a workaround I have used SET OPTION WAIT_FOR_COMMIT='ON', but this makes the load process much more slower.

If memory serves, running ALTER TABLE DROP FOREIGN KEY for all the foreign keys before loading the data, then ALTER TABLE ADD FOREIGN KEY again AFTER loading the data, runs pretty well.

...just the foreign key definitions, not the primary keys... that's a different discussion :)

permanent link

answered 19 Sep '18, 13:47

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

Thanks for the reply, this could be the solution, but the question is, is there a way to read all the configurations of FOREIGN KEYS so that I can read them again after dropping? OR, I should drop all FOREIGN KEYS on a table and then add them (and repeat this for each table having FK)? Could I use the SYSCONSTRAINT for example?

(19 Sep '18, 14:13) Baron

I would simply let Dbunload -n create the reload.sql. This includes a section with all FK declarations (as John has mentioned), so you can just use that section.

(19 Sep '18, 14:26) Volker Barth
Replies hidden

@Sarkis: Yes, what @Volker said, for the ALTER TABLE ADD FOREIGN KEY statements.

As for the ALTER TABLE DROP FOREIGN KEY statements, it's easier to write SQL code to generate those because you don't need all the crap details about columns, hints, options, blah blah blah :)

(19 Sep '18, 16:04) Breck Carter

dbunload creates a reload script that defines the tables with their primary keys, then loads the data, then does ALTERs to add the foreign keys. It shouldn't be possible to get key violations during the data loads.

permanent link

answered 19 Sep '18, 12:09

John%20Smirnios's gravatar image

John Smirnios
accept rate: 37%

But I have the case that I use DBUNLOAD only for data, without loading structure. The structure and the relationships between tables are already defined in my empty database, on which I inputing the resulted tables(.dat files).

(19 Sep '18, 12:13) Baron
Replies hidden

dbunload does not order the LOADs, just the creation of the keys. There are cases in which there is no ordering of LOADs that can be done in separate transactions without generating a key violation. For example, some schemas have circular PK/FK relationships.

(19 Sep '18, 12:19) John Smirnios

Another one would be CHECK constraints that relate to other tables whose data is not yet loaded. (Been there, done that...)

Note that this issue does not seem to apply here as Sarkis has to deal with FK violations.

(19 Sep '18, 12:25) Volker Barth

I am using the command 'dbunload -d .....' As a result I get a RELOAD.SQL file which contains only two groups of statements: (LOAD STATISTICS &&& INPUT INTO tablename FROM...) I dont see any statement in the RELOAD.SQL defining the constraints / creating PK/FK...

(19 Sep '18, 12:39) Baron
Replies hidden

A full unload without the -d would generate the other statements I'm talking about. In the general case, there is not always an ordering of data loads that will work so dbunload does work that way. If you happen to have a schema for which there is an ordering of LOADs which will work, you will need to order the data loads yourself according to your schema. Alternatively, if it is possible in your case, your 'empty' database should not have the schema either: let dbunload generate the schema definitions for you too.

(19 Sep '18, 12:44) John Smirnios
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 19 Sep '18, 11:48

question was seen: 1,275 times

last updated: 19 Sep '18, 16:05