Midrange News for the IBM i Community


Posted by: clbirk
speed
has no ratings.
Published: 28 May 2013
Revised: 30 May 2013 - 1426 days ago
Last viewed on: 25 Apr 2017 (2139 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.

 

chris

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

COMMENTS

(Sign in to Post a Comment)
Posted by: DaleB
Premium member *
Reading, PA
Comment on: speed
Posted: 3 years 10 months 27 days 8 hours 45 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: 3 years 10 months 26 days 13 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: 3 years 10 months 26 days 4 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.