Programming Languages on Power

 View Only

 Excessive temporary storage while updating CLOB fields via SQLRPGLE program

  • SQL
kumar sharath's profile image
kumar sharath posted 01/20/25 08:06 AM
Summary of the Issue :  
 A Batch job running under the HTTP server on IBM - I , meant for processing database updates to CLOBs using JSON_UPDATE SQL function shows excessive amount of temporary storage being consumed ( close to 8 GB ) 
Explanation of the process:
We have designed a new database file with a CLOB field meant to store JSON data in it.
The Service to update the JSON elements in the CLOB field is plugged into the end of an IBM I Job running under the IBM I - HTTP server meant to process  REST API calls from Online applications.
So on an average each API call involves updating around 10 to 15 fields that need to be updated in the individual JSON elements all stored in the CLOB field.
So the way we have implemented this is as below .
Step 1 : Read the JSON data from the CLOB from the new file and store into a global variable defined as  SQLTYPE : CLOB .
Step 2 : Now for every field that needs to be updated within a single API call , use the JSON_UPDATE SQL function to manipulate the SQLTYPE:CLOB variable .

 exec sql values

               (JSON_UPDATE(:jsonData, 'SET', trim(:path), trim(:newValue)

               )) into :jsonData;   

 

 

Note: We are repeatedly invoking this above function for every field being updated via the API call

 

 Step 3 : Now once all the fields are updated , we transfer the JSON value from the global variable to the database using an SQL update.
While implementing this approach , what we observed is , the SQL function JSON_UPDATE to do the JSON manipulation , is causing a sudden spike in Jobs temporary storage ( increases from 450MB to 3 GB  suddenly quite randomly while doing any of the updates). We are measuring this temporary storage in the bucket number greater than 65535 , meant for the job temporary storage.
I am failing to understand , if this is caused due to the repeated invocation of  JSON_UPDATE functions,  (or) could this be a memory leak (or) is there a PTF to check this.
Any thoughts on this please?

#SQL
Rich Malloy's profile image
Rich Malloy IBM Champions posted 01/21/25 03:16 PM

Kumar

Without knowing your current IBMi OS Level or patch Levels, I would encourage you to do 1 of 2 things, if not both

1 - you will prob want to open a case with IBM

2 - have you checked to see if your PTF levels on your system are 'current' with Temp Storage PTFs -> Temporary Storage PTFs

There are a 'few' that are in that list that point to JSON - you may want to check into them while you open the case with IBM to see what they have to say and what data they want you to collect. MGTOOLS has some options in there for collecting data for TMP Storage but again - I would open the case first with IBM and see what they want you to do. No point in collecting the wrong data and sending it to them blindly.  Good luck - Rich


#SQL
Satid S's profile image
Satid S posted 01/22/25 02:58 AM

Dear Kumar

It has been normal in my experience for DB2 for i engine to allocate a lot of run-time temp storage (in the range of single digit GB) while it runs SQL statements/Queries that handle a lot of data rows - especially when useful indexes are not present for DB2 engine to utilize.  But when your SQL statement(s) finishes, the temp storage should disappear. If not, then it's a bad sign.      

You mentioned your SQL handled 15 fields but did not mention the size of each field and how many rows were processed. Knowing these info would help you see if large GB run-time temp storage size is sensible or not.  To get these info, you can dump the plan cache with filters for the relevant date+time, user that runs the job(s), and tables being accessed to help you identify your SQL job(s) and then display Visual Explain of those relevant SQL statements of yours. The info in Visual Explain should give you some idea about how much data is being accessed here.

For each field being updated, did you update all rows in the table(s) or not ?  If not all rows (and assuming you used WHERE clause for UPDATE), did you have useful index(es) for the SQL statement?   If not, this can contribute to the larger allocated temp storage (but I cannot not be precise how much as this matter is not easy to know without investing more time and effort browsing Plan Cache for relevant info) due to high possibility of table scan operation rather than index probe.  Having proper indexes for WHERE, ORDER BY, GROUP BY, and join operations can help reduce run-time temp storage to some degree when large number of processed rows are involved.   Displaying Visual Explain of all SQL statements involved will give you more info on Index Advisor.  

>>>> Note: We are repeatedly invoking this above function for every field being updated via the API call <<<<

Did you run the update for one table field at a time? If you update more than one field at a time, the run-time temp storage can possibly be larger than the case when you update just one field at a time. 

One last factor that can help reduce run time temp storage allocation is in a principle of  "writing the SQL statement(s) that tries to eliminate as many irrelevant rows as early as possible before processing the relevant ones".  This deals in detail of the each statement itself.  

These are influencing factors I can think of right now. 


#SQL
Satid S's profile image
Satid S posted 01/22/25 03:58 AM

By the way, did you use the new QSYS2.HTTP function rather than the old SYSTOOLS.HTTP one?  This may also help with the temp storage size.

New HTTP functions based in QSYS2 at https://www.ibm.com/support/pages/new-http-functions-based-qsys2        

New QSYS2.HTTP Functions SQL at https://blog.faq400.com/en/db2-for-i/qsys2-http-functions-en/     


#SQL

Related Content