|
View previous topic :: View next topic
|
| Author |
Message |
Santhosh S M
New User
Joined: 04 May 2021
Posts: 9
Location: India
|
|
|
|
Hi All,
I have a PS file with data like index value of some rows of table stored in it.
How can I write a SQL query such that the SQL reads data from file and updates or deletes the data if found in table.
Regards,
Santhosh S M |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2018
Location: USA
|
|
|
|
Step 1: convert your “PS file” to the format equivalent to the unloaded DB2 table.
Step 2: use the DB2 Load utility to load the records from “PS file” to your DB2 table.
If only you opened any DB2 manual, you would find tons of examples how to do this. BTW, there are approx. 100500 other ways to do this job.
P.S.
The term “SQL query” has nothing to do with loading data into DB2 tables. |
|
| Back to top |
|
|
Santhosh S M
New User
Joined: 04 May 2021
Posts: 9
Location: India
|
|
|
|
Hi,
Thanks for your reply.
But I am trying to delete the rows from the DB2 which are there in the PS file. So is there any specific way i can achieve this..
So below is what I'm thinking of doing but don't know of this is possible or how to do it.
First read the file
Check if the row is present in the DB2 for the data read from file
If present delete it
Else continue to next record.
So I was wondering if this can be done without using programs
Regards,
Santhosh |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2018
Location: USA
|
|
|
|
First of all, please, try to make your questions well understandable to others. This discussion should not resemble an investigative interview.  |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2018
Location: USA
|
|
|
|
Step 0.1: extract key values from your file using any utility program, such as SORT, and generate SQL statement like
| Code: |
DELETE FROM ...
WHERE key_field IN (
‘Value1’,
‘Value2’,
. . . . . .
‘ValueNNN’ ) ; |
Step 0.2: execute the generated SQL using any batch tool, such as SPUFI
Then, if no error detected, proceed with steps 1 and 2 described above. |
|
| Back to top |
|
|
Santhosh S M
New User
Joined: 04 May 2021
Posts: 9
Location: India
|
|
|
|
Thanks for this, duly noted.
I will try the above steps and will get this sorted.
Regards,
Santhosh S M |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| Santhosh S M wrote: |
Hi All,
I have a PS file with data like index value of some rows of table stored in it.
How can I write a SQL query such that the SQL reads data from file and updates or deletes the data if found in table.
Regards,
Santhosh S M |
When you say INDEX value what are those ? Table key values ? If yes then, Isn’t this a simple program of delete ? Why do you want to do it DFSORT to form sql card, since it has its own limitations when you have thousands, millions of rows to delete. You would need much less efforts and save time if you write a program and it’s easy to maintain. |
|
| Back to top |
|
|
Santhosh S M
New User
Joined: 04 May 2021
Posts: 9
Location: India
|
|
|
|
Thanks for your time and response Rohit.
Will write a COBOL program to get this done, I was checking if this can be done without any program like simply by JCL and SQL. Like
Ps file has millions of index value(table key values)
SQL
Delete * from table name
Where column='value from ps file'
;
So I was trying to figure out how I can put table key value from ps to SQL query without cob or pl program
Regards,
Santhosh S M |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
You can do alternate way -
1. Unload the table into PS.
2. Use DFSORT JOINKEYS to join your input PS and step1 DS on key index and get unmatched records from step1 DS.
3. Use the output of step2 DS and load replace the DB2 table. |
|
| Back to top |
|
|
Santhosh S M
New User
Joined: 04 May 2021
Posts: 9
Location: India
|
|
|
|
Thanks Rohit, this approach looks easy and less work.
Thanks again for your help,,😀 I'll go with this one
Regards,
Santhosh |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| Good Luck! |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2018
Location: USA
|
|
|
|
| Santhosh S M wrote: |
I was checking if this can be done without any program like simply by JCL and SQL. Like
Ps file has millions of index value(table key values)
SQL
Delete * from table name
Where column='value from ps file'
;
So I was trying to figure out how I can put table key value from ps to SQL query without cob or pl program
Regards,
Santhosh S M |
| Code: |
//* Create dynamic SQL
//GENSQL EXEC PGM=SORT
. . . . . - standard DDs for the specific SORT version
//SORTIN DD * - or reference to your input to load
record1
record2
. . . . .
record123456789
//SORTOUT DD DISP=(NEW,PASS),DSN=&&NEWSQL,SPACE=......
//SYSIN DD *
SORT FIELDS=COPY
OUTFIL BUILD=(C’ DELETE FROM table_name ’,80:X,
/,C’ WHERE key_field = ’’’,1,keylen,C’’’ ;’,80:X)
END
//*
//* Run dynamic SQL via SPUFI
//RUNSQL EXEC PGM=IKJEFT01
//STEPLIB DD ..... - DB2 concatenation
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
RUN PROGRAM(spufiname) PLAN(planname) - ask your support, may be customized
//SYSIN DD DISP=(OLD,DELETE),DSN=&&NEWSQL
//* |
P.S.
In a well-designed database, with integrity constraints etc. some simplified approaches may not work, without a specially developed application program. |
|
| Back to top |
|
|
Santhosh S M
New User
Joined: 04 May 2021
Posts: 9
Location: India
|
|
|
|
Thanks for your help, I'll try this too. It's very useful for me.
Regards,
Santhosh S M |
|
| Back to top |
|
|
|
|
