IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

Need help on DB2 compound SQL or Inline SQL/PL


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

Active User


Joined: 22 Dec 2007
Posts: 116
Location: Bangalore

PostPosted: Fri Nov 29, 2019 3:49 pm
Reply with quote

Hi,

Need your advice on below scenarios.

One of the DW team trying to interact with DB2 on Z/OS tables. Now these tables have referential integrity among them. One of the main parent table and they have another 6 child tables. They are using an IDE - dbeaver to run their query.

1. It seems they are trying to delete huge number of rows (from all 7 tables) using an ADHOC delete query by joining all these tables. Delete query seems trying to delete around 29 million rows and it's only in Dev. In PROD it may be way more!!.

2. It seems after deleting around 2 million rows because of excessive locks tables gets locked and no more delete allowed. They told me that DBA told them about Current LOCKSIZE (PAGE) is 32KB.

When I told them if they can use multifetch cursor and use DELETE WHERE CURRENT OF in SP and they can issue DB2 COMMIT after certain number of deletes they told me that they are not allowed to create any SP. So that option gone.

They want to use Anonymous block of SQL code which I guess in DB2 for ZOS we call it Compound SQL/Inline SQL/PL through which they want to delete. Now I don't have much knowledge on this Compound SQL (I actually never got a chance to use it).

1. So first query is can we write compound SQL (like in BEGIN ATOMIC and END block) for DB2 on ZOS?

2. Got hit to this link - https://bytes.com/topic/db2/answers/615547-writing-compound-complex-sql

But in this link it is clearly written that we can not use COMMIT/ROLLBACK in compound SQL. Now I don't know what suggestion to give to them cause without COMMIT they delete query will lock the table.

If we can use compound SQL then someone please let me know if there is any detailed documentation on rules of compound SQL in DB2 for Z/OS? In this scenario what they can do to fix their delete? Will rewriting the DELETE query by using INDEX (if at all possible) can ease these delete operation??

So need help/suggestion on this problem. Please share your advice on this.

Thanks
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2508
Location: NY,USA

PostPosted: Fri Nov 29, 2019 10:57 pm
Reply with quote

I would think of this approach when delete rows are in such amount.
1. Unload the current DB2 table, DS-A
2. Keep the Rows to be deleted , DS-B
3. Use DFSORT to pick what's in DS-A and not in DS-B into DS-C
4. Load-Replace DB2 table using DS-C.
Back to top
View user's profile Send private message
subratarec

Active User


Joined: 22 Dec 2007
Posts: 116
Location: Bangalore

PostPosted: Fri Nov 29, 2019 11:10 pm
Reply with quote

Hi Rohit,

Thanks for your reply. Yes the solution you have provide applicable for Mainframe users. But they are from distributed team (DW and they are working on HADOOP cluster) and they neither have mainframe access nor they know how to work on Mainframe. They have common PROID they use to connect their IDE to DB2 LPAR on Z/OS.

That's the reason they keen to know if like ORACLE or DB2 LUW, DB2 on ZOS has this Compund SQL facility. If that facility available then they will make use of that. So by any chance do you know if they can use Inline SQL/PL ?
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2508
Location: NY,USA

PostPosted: Fri Nov 29, 2019 11:23 pm
Reply with quote

subratarec ,
The approach I listed can be done in Hadoop/DW, Instead of DFSORT DW needs to use inner joins/NOT EXISTS functions. Instead of DS-A, DS-B and DS-C they would need three temp tables.

I never worked on Compound SQLs and not sure if it ever allowed but if I get to know more will share here.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic All times are GMT + 6 Hours
Forum Index -> DB2
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Forum Replies
No new posts in-stream data set (inline data set) ... JCL & VSAM 4
No new posts RFE: Additional compound operators fo... PL/I & Assembler 1
No new posts Getting error - COBOL INLINE ERROR DI... CLIST & REXX 14
No new posts Applying compound condition DFSORT/ICETOOL 8
No new posts Inline Image copy with Reorg DB2 4

Back to Top