Stumped by SQL

[mwbrock@hotmail.com]

If anyone could help with this, I would certainly appreciate it. I have a CL program that needs to copy data into a temporary table, run a couple of RPG programs to update the table, and then produce summary output using SQL (I am replacing an old RUNQRY with RUNSQLSTM). The problem is that when the job reaches this final step, it hangs in a TIMW status, where it just sits and consumes CPU until I end it.

If I submit the RUNSQLSTM as a separate job after creating and updating the input table, it runs just fine and produces the summary output as expected. I had a similar problem on a CL I did last week which began to work correctly after I specified ALWCPYDTA(*NO) on the RUNSQLSTM. However, that doesn't work with this one for some reason.

I have used just about every method one could imagine to create the temporary table, including DYNSQL (from the SEQUEL product, specifying SERVER(*LOCAL)), another RUNSQLSTM to drop, create, and insert into the table, and good old CPYF frmrcd(1). All of this works just as expected, but I get the same results from the final step of creating the summary each time.

I have commented out the RPG applications that update the data to see if that was the issue...it isn't.

The CL looks like this:



RUNSQLSTM SRCFILE(mbsrc) SRCMBR(RESENGS) +
COMMIT(*NONE) ERRLVL(20)

............Drop table mbddl/tempfile;
............Create table mbddl/tempfile like mbrock/prodfile;
............Insert into mbddl/tempfile
............select * from mbrock/prodfile;

(have also used CPYF, etc instead of the above)

...rpg applications run here to update tempfile...

RUNSQLSTM SRCFILE(mbsrc) SRCMBR(crtsumtbl) +
COMMIT(*NONE) ERRLVL(20) ALWCPYDTA(*NO)

..........drop table mbrock/sumtable;

..........create table mbrock/sumtable as (
..........select '1' as breaklvl, ' ' as overflow,
.......... t.fld01, ((t.year*100)+t.month) as proddt,
.......... sum(t.fld02) as fld0201, sum(t.fld03) as fld0301,
.......... .
.......... ...etc for about 40 more fields...
.......... .
.......... sum(t.fld43) as fld4301, sum(t.fld44) as fld4401
.......... from mbddl/tempfile t
.......... where t.fld01 = 100
.......... and t.fldxx = '2'
.......... group by t.fld01, ((t.year*100)+t.month)
.......... order by t.fld01, proddt )
.......... with data;


The SQL is long and ugly because I am feeding a data warehouse and need to use the same field names that RUNQRY has been generating. Also, I needed to substitute the field and table names in this post for client discretion.

HELP?!?!?

[don.johnson31]

It sounds like the SQL Query is choking your machine. Many times this is beacuse the system is having to dynamically create indexes. You will first have to determine what indexes are needed if any.
To do this you can use a nifty feature called SQL Performance Monitor. You can access it multiple ways but I use iSeries Navigator also known as Ops Navigator. In the connection to your machine you will drill down into the Databases option and click on your database (many times is your machine name). Then you will see SQL Performance Monitor. Right click this option and then click new --> SQL Performance Monitor. A window will appear and will ask you questions about the monitor you want to perform. Depending on your version of Client Access you will find that there are different options. Basically you want to give the monitor a name and then select the schema (which means library). I recommend narrowing down to your job so that you dont get all kinds of crap in the monitor results. once you have finished going through this wizard type window and click finish, you will then run your query. i recommend having everything ready so you dont have to do data setup and such after the monitor has been started - therefore limiting the amound of stuff you are monitoring to just the query. after you have ran the query you will left click on the SQL Performance Montor option in Ops Nav again and in the right pane of the window you will see the montor in the list. right click it and click analyze. It will take a minute but a window will open and show a Summary. you can click on option in the list for "index creates advised". you will have to play around with the results but this should give you somewhere to start looking if the system is having to create indexes on the fly. I hope this helps.

[mwbrock@hotmail.com]

Thanks Don, I appreciate your response. I'm certain the query isn't choking the box...it easily handles much larger and more complex queries than this, across much larger tables as well. As well, this query runs just fine when submitted as a separate job after the input file is built (not an option to do that for this particular application, though, everything needs to run in a single job). The query also runs fine in interactive SQL.

I've run it through Visual Explain and there is an index recommended. I had not planned on doing that because this is a temp file that will go away after this query produces the summary file. However, I went ahead an built the index as an intermediate step after creating the temp file and producing the summary file. No luck. The job still hung up in a TIMW status until I ended it.

...Michael

[don.johnson31]

You may have already tried it but job log is always a good place to look. I wonder if there is a clue there.

[satid]

...The problem is that when the job reaches this final step, it hangs in a TIMW status, where it just sits and consumes CPU until I end it.

If I submit the RUNSQLSTM as a separate job after creating and updating the input table, it runs just fine and produces the summary output as expected.

When the job is in TIMW status and consuming CPU, you use the command WRKACTJOB and put option 5 in from of that job and press Enter. Then, in the subsequent menu that appears, you select option 10 Display Job log and press Enter, followed by F10. Then browse the last few messages in the log to see if there is any that looks out of place and could provide some clues as to what is happening. Move the screen cursor to the message line and press F1 to see more details of the message that may also indicate how to recover the issue.

Please also make sure you specify the parameter NAMING(*SYS) for the RUNSQLSTM command.

I also suspect you may have a low PTF level. What is the OS release you are using? Run the command DSPPTF and identify the topmost item name under "PTF ID" column to see if it is too low or not.

[mwbrock@hotmail.com]

Thank you, satid, I do appreciate your willingness to help. I've been on the AS/400 (and all its other names) since 1989.

Also, running V5R4, current on PTFs. :-)

[mwbrock@hotmail.com]

---You may have already tried it but job log is always a good place to look. I wonder if there is a clue there.

There is not, unfortunately. The last entry in the job log is just the RUNSQLSTM command. No further information.

[satid]

Since the problem occured in the CL program that you mentioned and the last RUNSQLSTM worked fine when you submitted it individually, you may modify that CL program to use SBMJOB for the last RUNSQLSTM to see if the problem is solved. If so, then we know we should focus on that CL program to investigate why the last RUNSQLSTM hangs when not submitted.

[mwbrock@hotmail.com]

Thanks, but submitting as a new job isn't an option for this particular application. When the primary job has completed, the summary file needs to be ready for download by the ETL processes for the data warehouse.

I have no doubt that submitting the final RUNSQLSTM as a new job would work just fine, but unfortunately that doesn't solve the actual problem. The job should not be hanging in TIMW to begin with...I guess I'll open up a database support call next week.

[R.Daugherty]

I haven't used RUNSQLSTM before, I have used STRQMQRY. When I Googled RUNSQLSTM every reference repeatedly pointed out that it doesn't support SELECT, including a Feb. 2007 paper with an IBM Infocenter V5R4 URL. Obviously it works for you at least once, I must be missing something.

Given what you have already tried including suggestions, in least change order I would:

- place second RUNSQLSTM in separate CL program and call

- place second RUNSQLSTM in separate CL program compiled with named activation group and call

- Replace second RUNSQLSTM with a STRQMQRY

Third option is guaranteed to work, no SELECT issues. I've used it in similar situations but now would use embedded SQL in an RPG program.

For anyone that thinks I'm a different rd who is embracing SQL now, no, I use the right tool for the job and sometimes SQL is the right tool. Sometiimes the specs call for it, whatever, we have all the tools on the iseries to pick and choose from.

rd

[efnkay]

Actually rd when you said...For anyone that thinks I'm a different rd who is embracing SQL now, no, I use the right tool for the job and sometimes SQL is the right tool. Sometiimes the specs call for it, whatever, we have all the tools on the iseries to pick and choose from.

You're not the only poster who has "put on a new coat" and apparently can see the huge benefits of using SQL at least now anyway...efnkay has promtoed the use thereof many many times in years past. Better late than never.

[R.Daugherty]

I haven't put on a new coat and there's no benefit to me. much less huge. However, the guy couldn't get his SQL to work and I looked up what he was doing and told him a couple of ways to make it work.

Never heard back from him though.

I use SQL for mass record accesses and always have. No change here.

rd

[mwbrock@hotmail.com]

I haven't used RUNSQLSTM before, I have used STRQMQRY. When I Googled RUNSQLSTM every reference repeatedly pointed out that it doesn't support SELECT, including a Feb. 2007 paper with an IBM Infocenter V5R4 URL. Obviously it works for you at least once, I must be missing something.

That works because it is a create table statement, not a select.



Given what you have already tried including suggestions, in least change order I would:

- place second RUNSQLSTM in separate CL program and call

- place second RUNSQLSTM in separate CL program compiled with named activation group and call

- Replace second RUNSQLSTM with a STRQMQRY

Third option is guaranteed to work, no SELECT issues. I've used it in similar situations but now would use embedded SQL in an RPG program.

Thanks, these are good ideas. I'll probably use the STRQMQRY.



For anyone that thinks I'm a different rd who is embracing SQL now, no, I use the right tool for the job and sometimes SQL is the right tool. Sometiimes the specs call for it, whatever, we have all the tools on the iseries to pick and choose from.

rd

I have the distinct advantage of not having a clue who you are or what banner you may wave in what circumstances. :-)

I am also a big believer in using the right tool for the job at all times. Thank you for your help!


...Michael

[R.Daugherty]

That is a distinct advantage! :)

Glad I was able to help, Michael. Good luck with it. The STRQMQRY worked well for me.

rd

[efnkay]

You can and may have many tools in your bag, and if one of them is SQL knowledge, take a deep breath and know this:

You can take your SQL knowledge and use it wherever you go. This knowledge can take you to any platform, and any database. Yes you can even use it in it's limited implementation (SQL92 ANSI standard) of the Synergex database. (ie. Used for SQL access to ISAM database files.)

Tell me about any other "tool" in your bag that goes between platforms, operating systems, programming languages, and databases, like your SQL knowledge can and will do for you...No other tool or knowledge you have will free you from "platform" type casting (We all experience.) unless one of the first things seen on your resume is SQL.

[shaffar]

If anyone could help with this, I would certainly appreciate it. I have a CL program that needs to copy data into a temporary table, run a couple of RPG programs to update the table, and then produce summary output using SQL (I am replacing an old RUNQRY with RUNSQLSTM). The problem is that when the job reaches this final step, it hangs in a TIMW status, where it just sits and consumes CPU until I end it.

If I submit the RUNSQLSTM as a separate job after creating and updating the input table, it runs just fine and produces the summary output as expected. I had a similar problem on a CL I did last week which began to work correctly after I specified ALWCPYDTA(*NO) on the RUNSQLSTM. However, that doesn't work with this one for some reason.

I have used just about every method one could imagine to create the temporary table, including DYNSQL (from the SEQUEL product, specifying SERVER(*LOCAL)), another RUNSQLSTM to drop, create, and insert into the table, and good old CPYF frmrcd(1). All of this works just as expected, but I get the same results from the final step of creating the summary each time.

I have commented out the RPG applications that update the data to see if that was the issue...it isn't.

The CL looks like this:



RUNSQLSTM SRCFILE(mbsrc) SRCMBR(RESENGS) +
COMMIT(*NONE) ERRLVL(20)

............Drop table mbddl/tempfile;
............Create table mbddl/tempfile like mbrock/prodfile;
............Insert into mbddl/tempfile
............select * from mbrock/prodfile;

(have also used CPYF, etc instead of the above)

...rpg applications run here to update tempfile...

RUNSQLSTM SRCFILE(mbsrc) SRCMBR(crtsumtbl) +
COMMIT(*NONE) ERRLVL(20) ALWCPYDTA(*NO)

..........drop table mbrock/sumtable;

..........create table mbrock/sumtable as (
..........select '1' as breaklvl, ' ' as overflow,
.......... t.fld01, ((t.year*100)+t.month) as proddt,
.......... sum(t.fld02) as fld0201, sum(t.fld03) as fld0301,
.......... .
.......... ...etc for about 40 more fields...
.......... .
.......... sum(t.fld43) as fld4301, sum(t.fld44) as fld4401
.......... from mbddl/tempfile t
.......... where t.fld01 = 100
.......... and t.fldxx = '2'
.......... group by t.fld01, ((t.year*100)+t.month)
.......... order by t.fld01, proddt )
.......... with data;


The SQL is long and ugly because I am feeding a data warehouse and need to use the same field names that RUNQRY has been generating. Also, I needed to substitute the field and table names in this post for client discretion.

HELP?!?!?
I didn't get the date this was posted, so the solution may have already been found or submitted, but if what you're trying to do is to get a SQL SELECT to return a temporary table, your best bet might be a User Defined Table Function (UDTF) since that's what it exists to do.

Hope this helps,
-
Steve Shaffar

[mwbrock@hotmail.com]

I didn't get the date this was posted, so the solution may have already been found or submitted, but if what you're trying to do is to get a SQL SELECT to return a temporary table, your best bet might be a User Defined Table Function (UDTF) since that's what it exists to do.

Hope this helps,
-
Steve Shaffar

Thanks Steve. I'm creating a temp file as a copy of a production file and then updating some of the records in the temp file. Then, I'm using that temp file as the input to create a summary file for download by our data warehouse.

A UDTF would probably work, but we are currently still at V5R4, moving to 6.1 later this year. So until then a UDTF would force the dispatcher to use the CQE, which is not what I want.

Regards,
...Michael

[the_weave]

Ditch the CL and write an Imbeded SQl program. Create your temp table, insert data, call you RPG programs, summarize data. I have a several programs in production similar to the simplified example below.


F************************************************* ********
F* INITIALIZATION
D************************************************* ********
C* -- Version: V5R4M0 060210
C* -- Generated on: 04/30/09 10:18:25
C* -- Relational Database: XXXXXXXXXX
C* -- Standards Option: DB2 UDB iSeries
C************************************************* ********
C/EXEC SQL
C+ Set option commit = *none
C/END-EXEC
C************************************************* ********
C/EXEC SQL
C+ DROP TABLE QTEMP/FTABLE
C/END-EXEC
C************************************************* ********
C/EXEC SQL
C+ CREATE TABLE QTEMP/FTABLE (
C+ Field1 CHAR(1),
C+ Field2 NUMERIC(3, 0))
C+ Field2 NUMERIC(3, 0))
C/END-EXEC
C************************************************* ********
C/EXEC SQL
C+ DROP TABLE QTEMP/FSUMMARY
C/END-EXEC
C************************************************* ********
C/EXEC SQL
C+ CREATE TABLE QTEMP/FSUMMARY (
C+ Sfield1 CHAR(1),
C+ Sfield2 NUMERIC(5, 0))
C/END-EXEC
C************************************************* ********
C* Get records and insert into temp file
C************************************************* ********
C/EXEC SQL
C+ INSERT INTO QTEMP/FTABLE (sfield1, sfield2)
C+ select FDATA1, FDATA2 FROM plibr/FREALDATA
C+ where Fdata3 = :P1 and Fdata4 = :P2
C/END-EXEC
C************************************************* ********
C* Call RPG Program
C************************************************* ********
C Call 'RPGPGM2'
C PARM FPARM1
C PARM FPARM2
C************************************************* ********
C* Summary Data
C************************************************* ********
C/EXEC SQL
C+ INSERT INTO QTEMP/FSUMMARY(Field1, field2)
C+ select Field1, sum(field2) from qtemp/FTABLE
C/END-EXEC
C************************************************* ********
C* END PROGRAM
C************************************************* ********
C Eval *INLR = *ON
C************************************************* ********
C* Initialization Subroutine
C************************************************* ********
C *INZSR BEGSR
C *ENTRY PLIST
C PARM P1 1
C PARM P2 8
C************************************************* ********

[efnkay]

There ya go "The weave"...No fixed source members to execute RUNSQLSTM.
Query Management queries are great for simple stuff where you may need to pass a "bind" variable or two...But RPG handles that all much better and with which most are more familiar.

My only preference would be to use the DECLARE GLOBAL TEMPORARY TABLE statement as opposed to CREATE TABLE QTEMP/xxxxxx because of the flexibility you have in creating a table in QTEMP from whatever columns you have in your "select list" and the ability to populate the table with the SELECT statement when used with "AS":
DECLARE GLOBAL TEMPORARY TABLE xxxxxx AS (SELECT.....FROM....WHERE...)
WITH DATA WITH REPLACE

The select statement can be as simple or complicated as you make it...The end result is a populated table in QTEMP which <btw> does not become a table accessible to the database. (Not found in SYSTABLES after you create it.) And it goes away like other QTEMP stuff obviously does when the job creating the QTEMP table ends.

[the_weave]

There ya go "The weave"...No fixed source members to execute RUNSQLSTM.
Query Management queries are great for simple stuff where you may need to pass a "bind" variable or two...But RPG handles that all much better and with which most are more familiar.

My only preference would be to use the DECLARE GLOBAL TEMPORARY TABLE statement as opposed to CREATE TABLE QTEMP/xxxxxx because of the flexibility you have in creating a table in QTEMP from whatever columns you have in your "select list" and the ability to populate the table with the SELECT statement when used with "AS":
DECLARE GLOBAL TEMPORARY TABLE xxxxxx AS (SELECT.....FROM....WHERE...)
WITH DATA WITH REPLACE

The select statement can be as simple or complicated as you make it...The end result is a populated table in QTEMP which <btw> does not become a table accessible to the database. (Not found in SYSTABLES after you create it.) And it goes away like other QTEMP stuff obviously does when the job creating the QTEMP table ends.
Thanks for the heads up on DECLARE GLOBAL TEMPORARY TABLE.

[efnkay]

My pleasure weave...Also let me just add something even more obvious for everyone...

DECLARE GLOBAL TEMPORARY TABLE xxxxxx AS
(SELECT.....FROM....(joins too) WHERE...GROUP BY...ORDER BY...UNION (another SELECT)) WITH DATA WITH REPLACE

By using the DECLARE GLOBAL TEMPORARY TABLE statement for gathering data, summarizing, sorting, prep'ing for display, or reporting, or export, import from, you name it...

I have not, I repeat HAVE NOT had the occassion or the need to use Query/400, QM Query's, RUNSQLSTM's, CRTLF command, or CREATE INDEX SQL statement in almost 5 (five) years...Remember before you were touched by the SQL angel, and a user wanted some inquiry or report in some god-forsaken-sort-order that you didn't have a keyed logical for...That's all gone now. Has been for some time.

<btw> The job that creates the "DECLARED GLOBAL TEMPORARY TABLE" can run SQL statements against the QTEMP table...Not in SYSTABLES just means its all yours to do with whatever you need. Don't worry about needing a DROP TABLE statement at some point either.

Say good-bye to CRTLF and those epic access-path rebuilds. (Of course you may need to CREATE INDEX if your query is building a temporary index...depends. How temporary is it? And how long does it take to build at runtime?)

[mwbrock@hotmail.com]

Ditch the CL and write an Imbeded SQl program. Create your temp table, insert data, call you RPG programs, summarize data. I have a several programs in production similar to the simplified example below.

Thanks for the example, Weave. Oddly enough, it seems nothing is working. Every suggestion (QMQRY, CL program in a new activation group, Embedded SQL, SQL Procedures, rclrsc in the CL,) is all just a different way of doing the same thing...

I did give a try, though (as well as the declare global temporary table), with the exact same results.

The problem continues to be that when the temp file is created in step one and then used as input to the summary file in step two, the job hangs in TIMW status. System status shows that it begins page faulting at the same rate it is paging no matter how much memory I allow the system to throw at it.

<pre>
Sys Pool Reserved Max ----DB-----
Pool Size M Size M Act Fault Pages
1 2716.64 352.58 +++++ .0 .0
2 1612.44 8.01 157 .0 .0
3 7521.28 .00 15 1477 1477
4 2310.09 .08 152 .0 .0
5 143.03 .00 10 .0 .0
</pre>

However...if I build the temp file (any which way I want), and then submit step two as a separate job (reading the temp file and building the summary output), it works just fine and completes in a few minutes without consuming excessive CPU or memory.

Well, we're supposed to upgrade our dev/test environments to 6.1 soon...that may take care of the problem.

[the_weave]

I wonder if the TIMW is happening because of a object lock or something similiar?

[mwbrock@hotmail.com]

I wonder if the TIMW is happening because of a object lock or something similiar?

Nope. I've found that the job will eventually finish. Takes about 30 minutes. Submitted as separate jobs, step 1 takes about 2.5 minutes and step 2 takes about 4.

[the_weave]

Nope. I've found that the job will eventually finish. Takes about 30 minutes. Submitted as separate jobs, step 1 takes about 2.5 minutes and step 2 takes about 4.

Weird! Have you tried STRDBG UPDPROD(*YES) and checked the joblog to see if there is any hint? Or perhaps you can use iSeries Navigator to do some SQL monitoring.

[mwbrock@hotmail.com]

Weird! Have you tried STRDBG UPDPROD(*YES) and checked the joblog to see if there is any hint? Or perhaps you can use iSeries Navigator to do some SQL monitoring.

Sure did. Nothing abnormal at all, just a longer runtime. SQLSTATE & SQLCODE are both 0, and there is a hard close on the cursor between step 1 and step 2. I do drop and re-create the summary file each time...so I'm going to try clearing it and repopulating instead and see if that does any better.

[R.Daugherty]

That's what I was going to suggest but you're a step ahead of me.

Given the additional info you found, it's sounds like it's contorting on a table created and populated in the process and then re-accessed in the process (at least with that complex break logic), incredibly with all those technologies.

After doing what you're doing on checking the drop/build part of it, I would change second SQL to a simple insert * from QTEMP/tbl to determine if just basic re-access or complexity of re-access. As I recall you said there wasn't a huge amount of data but seems like it's having a heck of a time ordering it for the break logic, etc. in a re-access.

rd

[mwbrock@hotmail.com]

Given the additional info you found, it's sounds like it's contorting on a table created and populated in the process and then re-accessed in the process (at least with that complex break logic), incredibly with all those technologies.


Yup, that's exactly what it's doing. Danged if I know why. Anyway, clearing and populating the table yields the same result. I'm going to go back to running the RPG program for step 1 (which takes 8 minutes), and then run step 2 as a query (replacing a RUNQRY).

[efnkay]

Wow...This thread has legs...!!! Definitely a contention issue with the "id", or the "self", as-in programs-access-to-files-owned-created-by-self. I'd love to see the Visual Explain explain this... :-) This tends to make me suspect the "missing PTF" theory if that wasn't covered in this thread earlier.

Knowing a submitted job gets it's own copy of QTEMP of the submitter, without all the knicks and cuts of existing jobs access "locks", no matter how benevolent they may be...It's gotta be either a contention issue or maybe
related to a mystery I've had some time ago.

For whatever reason a particular SQL statement I was debugging (because all of the sudden it started to take REAL long to run and complete...) So I'm running the exact statement from STRSQL that runs embedded in RPG in a
batch process. BANG! runs so fast I can't see anything. <btw> This was a DECLARE GLOBAL TEMP-yada-yada TABLE statement.

But when it ran in batch the job indicated yada-yada-IDX...!!! A temporary index was being built when running in batch. What the...run again, again, again. Won't build IDX running in SQL session. Hmmm...So I changed one non-join qualification in the WHERE clause on a date field like (field = date) to (field >= date)...Big deal right? And it's not part of a GROUP BY or ORDER BY clause. BOOM! Now it builds a temp index running interactively. What the...???

Didn't resolve it though. I made it go away by writing a better (more
efficient) SQL statement to arrive at the same result by optimizing out a file joined in the statement. ie. (Eliminated one of the files used in the join simply to get to a 3rd file join but no fields were in the select-list of the resulting table.) I used EXISTS predicate and sub-select of the file removed from the join, basically "joining" through an EXISTS check instead.

Ran the "better" SQL statement in batch and interactively a gazillion-times and neither which-way ever built a temporary index again. Sometimes the beauty is in the eye of the CQE or SQE optimizer...and how he/she thinks is best way to execute your SQL statement. Geat stuff guys...er, I mean contributing persons!!! :-)

[R.Daugherty]

Yes, it's all in the eye of the optimizer. For whatever reason it's decided that brute force (that is, no index) is best access method given this particular situation.

One way to give it a clue is to create the index after the create insert statement, then specify the index for the insert from in the second create. If that doesn't work then there's something about the file indexing that is not re-accessable until finalized when the job ends. Maybe some way to close and reopen the file would finalize it internally mid-job.

But what I would do is have a permanent physical file keyed by desired index for this operation and delete * from, then copy the first temporary file into it after it's created, then access the permanent physical file with the second insert from.

If necessary you could even lock down the SQL access logic in a package against the permanent file.

We just have to give that optimizer a little bit of help. :)

rd

[mwbrock@hotmail.com]

One way to give it a clue is to create the index after the create insert statement, then specify the index for the insert from in the second create. If that doesn't work then there's something about the file indexing that is not re-accessable until finalized when the job ends. Maybe some way to close and reopen the file would finalize it internally mid-job.

Tried creating the index over the temp file before populating it as part of step 1. Same result. I've inserted a RCLRSC between the steps, so that should've closed the file. SQL monitor shows a hard close of the cursor as well.



But what I would do is have a permanent physical file keyed by desired index for this operation and delete * from, then copy the first temporary file into it after it's created, then access the permanent physical file with the second insert from.

Trying something similar...crtdupobj of the original physical, keyed file into qtemp and using that as my temp file. Perhaps that will work...I'd hate to copy the temp file into a shell a second time...that's double the time to accomplish. We're talking about 11mm records.




We just have to give that optimizer a little bit of help. :)

rd


Not for what it costs. :-)

[garylavery@aol.com]

1) I run "strings" of SQL statements (fairly complex) from a PC source file. Once in a while a statement will hang because the previous statement seems to "lock" a file. Always an Update statement. In my case a timeout occurs and I get a message. Possibly your RPG program is not releasing resources quickly enough before getting to the problem statement. Sorry I'm not that technical - I just know it happens. I just restart at the stuck statement & the job moves on.

2) Your "Group By" clause...
a. Make sure those fields cannot be null (i.e. accidentally).
b. Try replacing the inline multiplication with a static "2009".

Again, I'm not that deep technically but the above 2 gotchas in Group By clauses have bitten me more than once and always with very strange symptoms.

Hope this helps - Gary

[R.Daugherty]

He had commented out the RPG calls. They were not an issue.

The SQL statement works just fine if the statement is submitted to a new job, so nothing about the syntax, data, etc. is an issue.

It has been narrowed down to subsequent access to a created file in same job with this particular configuration. I didn't see much in the way of efforts at less complex re-accesses to see if they worked as this was required in the end.

rd

[mwbrock@hotmail.com]

Went to 6.1 over the weekend, re-created the SQL procedures (which is what I'm using for all this now). Same result.

Finally set option ALWCPYDTA=*NO on step 2...and it works like a champ!

'Splain that...

[the_weave]

Went to 6.1 over the weekend, re-created the SQL procedures (which is what I'm using for all this now). Same result.

Finally set option ALWCPYDTA=*NO on step 2...and it works like a champ!

'Splain that...

Sounds like a call to Support would be in order.........

[efnkay]

Don't recall if you tried ALWCPYDTA=*NO before, but that kinda still hints at PTF's that may not have been there or this was a problem fixed in 6.1. Heck anyway! Glad you you were able to put it behind you. Onward and upward.

[R.Daugherty]

Since it wasn't tried before, the assumption should be that it would have worked before, not been changed or fixed by 6.1.

And, anyway, what is "it" we're talking about? A clearly documented parameter that clearly states that SQL performs faster on live data, that is, when a copy of the data, in this case, 11 million rows, isn't made. It's much more complicated than that, I'm sure, but we always knew that it was stumbling on re-accessing the just built data and specifying that the data just built was to be accessed versus a copy of it allowed the optimizer to perform as it would if the file was closed out and the second SQL run in a new job.

I don't particularly see this as greatly surprising. There is also ALWCPYDTA *OPTIMIZE which may also have worked perhaps even better.

The optimizer is just code that people wrote, and obviously in complex decsionmaking it will not always make optimal decisions. That's why optimizing hints and locking down the decision in a package are provided for, and fall back to brute force not what I would call an operating system problem.

Having said that, brock, it was a pleasure sitting back and watching you do all the work. :) Great to see you have that solved and in production.

rd

[efnkay]

Whatever the reason it didn't work before, you got it working now. Regardless of the cause, at least nobody suggested you scrap the SQL and do it with native I/O and RPG.
Note: They still make hand tools if you prefer. But most any tool you can think of now is battery-powered and does the "turning" for you with the press of a button.

Kinda how I compare SQL I/O to native programming I/O regardless of programming language. And the SQL stays the same regardless of the program language. (Given that different syntax's for same functionality exist in various databases.)

[mwbrock@hotmail.com]

And, anyway, what is "it" we're talking about? A clearly documented parameter that clearly states that SQL performs faster on live data, that is, when a copy of the data, in this case, 11 million rows, isn't made.

-------------
Allow copy of data (ALWCPYDTA)
Specifies whether a copy of the data can be used in a SELECT statement.

*OPTIMIZE
The system determines whether to use the data retrieved directly from the database or to use a copy of the data. The decision is based on which method provides the best performance. If the Commitment control (COMMIT) parameter is not *NONE, the Allow blocking (ALWBLK) parameter should be set to *ALLREAD, when possible, for best performance.
*YES
A copy of the data is used only when necessary.
*NO
A copy of the data is not used. If a temporary copy of the data is required to perform the query, an error message is returned.
-------------

Clearly...


I don't particularly see this as greatly surprising. There is also ALWCPYDTA *OPTIMIZE which may also have worked perhaps even better.


I thought so as well, which is why that was my default. But as you mention, the optimizer is just lines of code that people have written. It was making the less than optimal decision in this case.

Anyway...I'm happy it works now.

...Michael