Load utility

[bgdeepak]

Hi,
I am using the load utility to load data from the files.The load inserts the data in the sequence in which data is present for most files.For some files the load utility does not insert the data in the sequence it should insert.The last line gets inserted in the middle.Is there a way by which i could specify the load utility to load the data in the sequence in which it is present in the files?How do i go about this..

With Regards,
Deepak B G

 

[sathyarams]

What do you mean the last row gets inserted in the middle ?



For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

 

[bgdeepak]

Hi,
Assume the flat file with contents as shown below
A|x|y|z --row1
B|q|a|a --row2
C|s|s|s --row3

It inserts row1 into the table first and then row3 and then row2.
i.e.,
row1
row3
row2

but i want the order of insertion into the table as
row1
row2
row3

Thanks,
Deepak B G

 

[gregsimpson]

Deepak,

you are inserting into a Relational Database. As such the order in which the records appear to you to be stored have no relevance.

Should you require a certain order on retrieving from the database you should use the ORDER BY CLAUSE. You should not rely on what you think to be the physical placement of rows unless order is not important for you.

Greg

 

[bgdeepak]

hi gregsimpson,
The order in which the data is inserted in the table is important. The records should be visualized as a tree structure and the records are inter-related.So order plays an important role.

Thanks,
Deepak B G

 

[gregsimpson]

Deepak,

this brings me back to what I said earlier, if order is important then use an ORDER BY on your retrieval.

Have I missed something here? Can anyone else help Deepak out with his problem?

Greg

 

[stevexff]

if the data form a tree structure, as Deepak says, then shouldn't there be a parent column on each row, which contains the key of the 'parent' row? This would allow the tree to be nested to any depth. I'm assuming here that the root level of the tree would have a parent key of zero.

As Greg and sathyarams have already noted, this isn't like a flat file. You can't rely on the insertion sequence in a relational table, as this is not defined, in other words it depends on the access path chosen by the optimiser and may change in the future. Like if you add other indexes, reorganise the table, or change releases of DB2.

Steve

 

[bgdeepak]

Hi stevexff,
The hierarchical structure does not contain the information about who the parent is?I will have to deduce it from the file.
Example: ASSUME THAT THE FLAT FILE CONTAINS THIS:
H header info
S store identifier s1
A store information about S1
T trailer info
H header info
S store identifier S2
A store information about s2
T trailer info
H header info
S store identifier s1
A store information about S1
T trailer info
I Will have to deduce the parent information.saying that A belongs to either store s1 or store S2.Now suppose the load utility does not maintain the order of insertion into the table,then i could miss this information.
Suppose load utility loads the data in this way into the table :
H header info
S store identifier s1
T trailer info
A store information about S1

i would not have any clue that A belongs to store S1 and there are no columns in A which could give me this information that A belongs to store S1.Thats why the order was important while inserting.Can i explicitly tell the load utility to maintain the order of insertion.Thats what my question was.I am sorry if i had confused you guys.

Thanks,
Deepak B G

 

[stevexff]

Deepak

When you design your tables, the idea is to make the rows on the table represent a particular entity in your data model. Generally speaking, four different record types means four different tables. But some of these may not be needed.

In your example, it seems like what you really need is a store table. Each row should contain columns of information about the store that it represents. In other words, attributes from the S and A records.

Is the structure exactly as described above (HSATHSAT...) or can you have multiples within it, e.g. HSAASAAAT...

Unless there is significant information that you need in the H and T records, I don't think you should be adding them to the table at all, except possibly as attributes of store - like audit_timestamp, loaded_by etc.

If you have repeating groups of A records, you might need another table to hold them, with a foreign key on the second table.

Steve