Hi All,

I would like to ask for a favour.

I am new to writing sql procdure.

I have three tables sax XDF00, YDF00 and ZDF00. I would like to create a sql procedure, which first delete all the records from XDF00 (which will inturn delete all the records from YDF00, which has foreign key relatioinship with XDF00). After deletion, based on the value of a particular field say ref.ZDF00(a sequence possibly, ZDF00 has also foreign key relationship with XDF00) if it exists, we need to insert records in XDF00 for ref.ZDF00 else insert new values.

Could you please advise?

Cheers
coolboy

I thought that no special difficulty in your requirements.
It will be enough to DELETE/INSERT accoding to your required sequence.
I didn't see the neccesity of LOOP, at least from your description.

Some operations may be simplified by specifying appropriate rules on referential constraint clause.
For example, rows in YDF00 references XDF00 can be deleted by ON DELETE CASCADE.

Another example:
It may be possible to do by one insert statement for first(if it exists) insert in XDF00. Like this:
INSERT INTO XDF00
SELECT ....
FROM ZDF00
WHERE ....

> else insert new values.
From where do you get the new values?

Hi,

Many thanks for your reply.

Initially we were using macro (vb script) in AS400, to update XDF00. The set {x,y,z} where y and z are sequences forms the primary key for XDF00, YDF00 holds the extension details of XDF00. Due to some technical difficulty, we would like to use sql procedure from now on.

ZDF00 will be updated with a reference to {x,y,z}, when we update a record for {x,y,z} via front end.

Since some of the entries in XDF00 got corrupted, we would need to delete all the records and reinsert all of them with out affecting the records for {x,y,z} in ZDF00.

Could you please advise if it is possible using a sql procedure to get the array of {x,y,z} in its proper order from ZDF00? The for each of the distinct {x,y,z} in the array, we would need to make sure records are available in the XDF00. The values for other fields will nee to be manually inserted in to the insert statements.

Once we have records in XDF00 for all available {x,y,z} in ZDF00 in the proper order, we will need to insert some more records as the following sequences.

Cheers
coolboy

I couldn't understand what you want to do exactly.

Please give me DDLs, sample data before the procedure run and expected data after the procedure run, for all three tables,
and parameters of the procedure.

It is not necessary to describe the process(or logic) in the procedure, at least now.
In other words, please supply enough sample data to understand what should do in the procedure.

Are you using DB2 for iSeries? What version/release are you using?

XDF00 (sample data before deletion)

X Y Z

X1 001 1 PAN CANCEL 0
X2 001 1 RAN BEGIN 0
X3 001 1 CAN GH_CANCEL 0
X4 001 1 PAN CANCEL 0
X5 001 1 RAN BEGIN 0
X6 001 1 CAN GH_CANCEL 0
X7 001 2 INM P LIVA05 0
X8 001 3 INK P KIVA05 0
X9 001 4 INC P RIVA05 0
X1 001 9 PAN CANCEL 0
X2 001 8 RAN BEGIN 0
X3 001 6 CAN GH_CANCEL 0
X4 001 6 PAN CANCEL 0
X5 001 8 RAN BEGIN 0
X6 001 7 CAN GH_CANCEL 0
X7 001 7 INM P LIVA05 0
X8 001 5 INK P KIVA05 0
X9 001 3 INC P RIVA05 0

We need to delete all the records from XDF00 as the data is corrupted. We may use delete * from XDF00.

Now we need to get the distinct occurrences of X,Y,Z in ZDF00 and re-insert records for each X,Y,Z found

ZDF00 (sample data)

X Y Z (Foreign Key)

100,000,003 0 X1 002 9 035236 001
100,000,004 0 X2 001 2 007369 005
100,000,005 0 X2 002 6 035236 001
100,000,006 0 X3 001 3 007369 003
100,000,007 0 X1 001 9 007369 004
100,003,229 0 X4 001 1 038994 001
100,003,230 0 X8 001 6 038995 001
100,003,231 0 X3 001 5 038995 001
100,003,232 0 X5 001 10 038995 001
100,003,239 0 X9 001 3 038998 001
100,003,240 0 X8 001 7 038998 001
100,003,241 0 X3 001 8 038998 001
100,003,244 0 X1 001 6 039001 001
100,003,245 0 X5 001 4 039001 001
100,003,246 0 X4 001 10 039001 001

XDF00 (sample data after insertion based on entries in ZDF00)

X Y Z

X4 001 1 - - -
X2 001 2 - - -
X3 001 3 - - -
X9 001 3 - - -
X5 001 4 - - -
X3 001 5 - - -
X2 002 6 - - -
X8 001 6 - - -
X1 001 6 - - -
X8 001 7 - - -
X3 001 8 - - -
X1 002 9 - - -
X1 001 9 - - -
X5 001 10 - - -
X4 001 10 - - -


Newly added

L3 001 8 - - -
L1 002 9 - - -
L1 001 9 - - -
L5 001 10 - - -
L4 001 10 - - -
L5 001 11 - - -

We are using DB2 for iSeries.



Cheers
Coolboy

I couldn't see the necessity of more than the following two statements for the procedure.
There is no implied sequence in DB2 tables.
For example, I saw the following statement in "Chapter 4. Queries P445" in "DB2 for i5/OS SQL Reference Version 5 Release 4".

Dear tonkuma,

Many thanks for your reply. The code was really helpful.

Cheers
Coolboy