Stumped by SQL

[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]

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

[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.

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

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.

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.

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?)

[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 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.

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 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]

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************************************************* ********

[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

[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

About that SQL...

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.

[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

[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]

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

Stumped by SQL

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 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