Midrange News for the IBM i Community

Posted by: clbirk
has no ratings.
Published: 28 May 2013
Revised: 30 May 2013 - 3970 days ago
Last viewed on: 10 Apr 2024 (4137 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

speed Published by: clbirk on 28 May 2013 view comments(3)

I had 135,000 sql statements to process against 5 different files. I had the jr. programmer write out sql statements (meaning write out of a program, not "write" with a pen), and then I copied like 45,000 into inav's sql interface to run. 


Slow was an understatement. Took like 5 or more hours for those 45,000.


So for the 90,000 remaining I found runsqlstm and while it asks for a source file and member, it didn't like my flat file in qs36f, so I simply dumped them to the ifs and used the stream file option.  Took maybe 10 minutes to do the rest.


While the inav feature is nice as it will show you an error, slow is the nature of the beast. Sure I use it alot when I have a dbfile that I need to reorganize some fields or such that inav (SQL/DDL) won't let you do, as I will simply click generate sql, take the sql make my changes and go delete the file and then run that window.  But if you have alot of transactions, it is not the way to go.



Return to midrangenews.com home page.
Sort Ascend | Descend


(Sign in to Post a Comment)
Posted by: DaleB
Premium member *
Reading, PA
Comment on: speed
Posted: 10 years 10 months 15 days 16 hours 46 minutes ago

For future reference, you can CPYF a flat file into a source file. See SRCOPT() and SRCSEQ() keywords (though defaults are probably OK for what you describe). One advantage of this is relative ease of using a user-defined PDM option to do the RUNSQLSTM. Or to do the CPYF, for that matter.

Posted by: Paulster
Premium member *
Sweden and The Netherlands
Comment on: speed
Posted: 10 years 10 months 14 days 21 hours 46 minutes ago

...this just leaves one curious as to the sheer number of SQLs to be run...

Posted by: clbirk
Premium member *
Comment on: speed
Posted: 10 years 10 months 14 days 12 hours 41 minutes ago

These were all delete statements and to delete records out of 5 tables. Every couple of months we go through and delete these out of our system as these 5 tables are used in an order entry system (that once a phone order is placed it goes into the regular erp system). These tables are used by php code on the box to do a gui order entry system.


Yes I could run them every week (or every day) and get rid of those 90 days or oolder but but I hadn't ran it for a while. I was going to directly delete them in an rpg program but the jr. programmer was to write it and further more there are different keys and it is a situation of in some cases getting keys from various files also doing setll, etc. and so I wanted to see the transactions.