Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
Posted: Fri Dec 10, 2004 5:14 am Post subject:
Rama_Prayaga,
Yes you can use DSNUTILB to update and insert rows into the table. The easiest way to solve your problem is to delete off the existing rows for the keys you want to update and perform a LOAD RESUME. This will insert all the rows as new rows.
Then I have to write a program to check that and delete it.Also there could be some data which will is is not present in the file and present in the table.If i do load resume it will delete the existing data in the table.
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
Posted: Fri Dec 10, 2004 8:32 am Post subject:
Rama_Prayaga,
You don't need a program to delete the data from the table. How do you get the data to be updated or inserted? How do you generate the file to be used as input to DSNUTILB? Let me know the details and then we will show you the way to do it.
Quote:
If i do load resume it will delete the existing data in the table.
Load RESUME does not delete the existing data in the table.
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
Posted: Mon Dec 13, 2004 10:18 am Post subject:
Quote:
The files will be my input, I have extract the data from the file and load it into the table.
Rama_Prayaga,
I assmuing that your input file has the following layout.LRECL=80,RECFM=FB
Code:
PRIMARY-KEY PIC X(05).
FILLER PIC X(75).
we first will generate the delete statements for all the records in the file. Since we are deleting records we need a commit after every 500 records. The following fileaid steps will create the DELETE control cards for all the records in your file.
Rama
Code:
//************************************************************
//* THE FIRST STEP WILL PUT AN "X" IN POSITION 80 OF THE *
//* INPUT FILE OF 500TH RECORD. *
//************************************************************
//STEP0100 EXEC PGM=FILEAID,REGION=4M
//SYSPRINT DD SYSOUT=*
//SYSLIST DD SYSOUT=*
//SYSTOTAL DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//DD01 DD DSN=YOUR INPUT FILE,
// DISP=SHR
//SYSIN DD *
$$DD01 UPDATE SELECT=500,R=(80,C'X'),OUT=0
/*
//************************************************************
//* THE SECOND STEP WILL WRITE OUT EVERY RECORD AND *
//* IF AN "X" IS FOUND IN POSITION 80, A NEW RECORD WITH *
//* COMMIT WILL BE WRITTEN AFTER EVERY 500TH RECORD. *
//************************************************************
//STEP0300 EXEC PGM=FILEAID,REGION=4M
//SYSPRINT DD SYSOUT=*
//SYSLIST DD SYSOUT=*
//SYSTOTAL DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//DD01 DD DSN=YOUR INPUT FILE,
// DISP=SHR
//SQLCARDS DD DSN=YOUR OUTPUT SQL DEL STATEMENT FILE,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(15,5),RLSE)
//SYSIN DD *
$$DD01 USER IF=(80,NE,C'X'),
MOVE=(1,80C' '),
MOVE=(1,C'DELETE FROM TABLE'),
WRITE=SQLCARDS,
MOVE=(1,80C' '),
MOVE=(1,C' WHERE PRIMARY KEY = '),
MOVE=(+0,C"'"),
MOVE=(+0,5,1),
MOVE=(+0,C"'"),
WRITE=SQLCARDS,
MOVE=(1,80C' '),
MOVE=(1,C';'),
WRITE=SQLCARDS,
IF=(80,EQ,C'X'),
MOVE=(1,C'DELETE FROM TABLE'),
WRITE=SQLCARDS,
MOVE=(1,80C' '),
MOVE=(1,C' WHERE PRIMARY KEY = '),
MOVE=(+0,C"'"),
MOVE=(+0,5,1),
MOVE=(+0,C"'"),
WRITE=SQLCARDS,
MOVE=(1,80C' '),
MOVE=(1,C';'),
WRITE=SQLCARDS,
MOVE=(1,80C' '),
MOVE=(1,C'COMMIT;'),
WRITE=SQLCARDS
/*
Once these delete cards are created all you need to do is run them with DSNTIAUL
I have one more question:
If I want to update some field of existing record, not delete the old one and insert the new one, How to do?
Unload the table and using SORT to generate the Load input?
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
