IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

[Solved]DB2 SQL query to read and update data from a file


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Santhosh S M

New User


Joined: 04 May 2021
Posts: 9
Location: India

PostPosted: Sat Jun 05, 2021 5:13 pm
Reply with quote

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
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1140

PostPosted: Sat Jun 05, 2021 7:41 pm
Reply with quote

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
View user's profile Send private message
Santhosh S M

New User


Joined: 04 May 2021
Posts: 9
Location: India

PostPosted: Sat Jun 05, 2021 7:56 pm
Reply with quote

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
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1140

PostPosted: Sun Jun 06, 2021 1:57 am
Reply with quote

First of all, please, try to make your questions well understandable to others. This discussion should not resemble an investigative interview. icon_rolleyes.gif
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1140

PostPosted: Sun Jun 06, 2021 2:06 am
Reply with quote

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
View user's profile Send private message
Santhosh S M

New User


Joined: 04 May 2021
Posts: 9
Location: India

PostPosted: Sun Jun 06, 2021 9:40 am
Reply with quote

Thanks for this, duly noted.

I will try the above steps and will get this sorted.

Regards,
Santhosh S M
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2723
Location: NYC,USA

PostPosted: Sun Jun 06, 2021 10:14 am
Reply with quote

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
View user's profile Send private message
Santhosh S M

New User


Joined: 04 May 2021
Posts: 9
Location: India

PostPosted: Sun Jun 06, 2021 4:42 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2723
Location: NYC,USA

PostPosted: Tue Jun 08, 2021 3:34 am
Reply with quote

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
View user's profile Send private message
Santhosh S M

New User


Joined: 04 May 2021
Posts: 9
Location: India

PostPosted: Tue Jun 08, 2021 7:01 am
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2723
Location: NYC,USA

PostPosted: Tue Jun 08, 2021 11:41 pm
Reply with quote

Good Luck!
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1140

PostPosted: Wed Jun 09, 2021 1:33 pm
Reply with quote

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
View user's profile Send private message
Santhosh S M

New User


Joined: 04 May 2021
Posts: 9
Location: India

PostPosted: Wed Jun 09, 2021 4:11 pm
Reply with quote

Thanks for your help, I'll try this too. It's very useful for me.

Regards,
Santhosh S M
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts DB2: Need helping below DB2 query DB2 13
No new posts To pull the file with Date and Time u... TSO/ISPF 16
No new posts Data for newly added fields not displ... IMS DB/DC 5
No new posts regarding COBOL WRITE verb (VB File H... COBOL Programming 6
No new posts How to read IPv6 from ADDR_INFO in EZ... All Other Mainframe Topics 0
Search our Forums:

Back to Top