Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Wed Mar 08, 2006 10:57 am Post subject: SQL Stored Proc with Global Temp Table
I'm creating a SQL procedure in Development Center. I have never user Global Temporary Tables but I gave it a try. I believe I was able to create the table, but when I try and use the Temp Table in a cursor the procedure will not compile. Below is the procedure along with the errors.
Any help will be appreciated.
Code:
CREATE PROCEDURE FDBMD.RETRIEVE_PROCEDURES_CURRENT ( IN P_PLAN_TYPE VARCHAR(5) )
RESULT SETS 1
LANGUAGE SQL
COLLID FODBCOLL
WLM ENVIRONMENT WLMTENV4
RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&172.16.5.89:*)'
P1: BEGIN
DECLARE GLOBAL TEMPORARY TABLE T
( PLAN_TYPE VARCHAR(2) NOT NULL,
PROC_CODE VARCHAR(5) NOT NULL,
SEQ_NBR INTEGER NOT NULL );
INSERT INTO T
SELECT PLAN_TYPE, PROC_CODE, MAX(SEQ_NBR)
FROM FDBMD.PROCEDURE_ACTIVE
WHERE PLAN_TYPE = P_PLAN_TYPE
GROUP BY PLAN_TYPE, PROC_CODE;
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT P.PLAN_TYPE, P.PROC_CODE, P.PROVIDER, P.MODIFIER, P.PLACE_OF_SERV, P.BILL_TYPE, P.DIAGNOSIS,
P.RULE_SET_ID, 0, 0, 0, 0
FROM FDBMD.PROCEDURE_ACTIVE P
INNER JOIN T ON P.PLAN_TYPE = T.PLAN_TYPE AND
P.PROC_CODE = T.PROC_CODE AND
P.SEQ_NBR = T.SEQ_NBR ;
OPEN cursor1;
END P1
Code:
FDBMD.RETRIEVE_PROCEDURES_CURRENT - Build started.
Calling the build utility SYSPROC.DSNTPSMP...
STEP FILE SEQN LINE
-------------- -------------- ----------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CONVERT SQLPRINT 1 1DB2 SQL PRECOMPILER VERSION 8 REL. 1.0 PAGE 1
CONVERT SQLPRINT 2 0
CONVERT SQLPRINT 3 0OPTIONS SPECIFIED: STDSQL(NO) NEWFUN(YES) MAR(1,80) HOST(SQL) ONEPASS CCSID(37) MAR(1,80)
CONVERT SQLPRINT 4 0OPTIONS USED - SPECIFIED OR DEFAULTED
CONVERT SQLPRINT 5 APOST
CONVERT SQLPRINT 6 APOSTSQL
CONVERT SQLPRINT 7 ATTACH(TSO)
CONVERT SQLPRINT 8 CCSID(37)
CONVERT SQLPRINT 9 NOPADNTSTR
CONVERT SQLPRINT 10 CONNECT(2)
CONVERT SQLPRINT 11 DEC(15)
CONVERT SQLPRINT 12 FLAG(I)
CONVERT SQLPRINT 13 HOST(SQL)
CONVERT SQLPRINT 14 LINECOUNT(60)
CONVERT SQLPRINT 15 MARGINS(1,80)
CONVERT SQLPRINT 16 NEWFUN(YES)
CONVERT SQLPRINT 17 ONEPASS
CONVERT SQLPRINT 18 OPTIONS
CONVERT SQLPRINT 19 PERIOD
CONVERT SQLPRINT 20 NOSOURCE
CONVERT SQLPRINT 21 STDSQL(NO)
CONVERT SQLPRINT 22 SQL(DB2)
CONVERT SQLPRINT 23 NOXREF
CONVERT SQLPRINT 24 1DB2 SQL PRECOMPILER MESSAGES PAGE 2
CONVERT SQLPRINT 25 0DSNH016I E DSNHPARS LINE 21 COL 13 "GLOBAL" REQUIRED
CONVERT SQLPRINT 26 DSNH104I E DSNHPARS LINE 21 COL 13 ILLEGAL SYMBOL "CURSOR1". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: GLOBAL
CONVERT SQLPRINT 27 1DB2 SQL PRECOMPILER STATISTICS PAGE 3
CONVERT SQLPRINT 28 0SOURCE STATISTICS
CONVERT SQLPRINT 29 SOURCE LINES READ: 32
CONVERT SQLPRINT 30 NUMBER OF SYMBOLS: 12
CONVERT SQLPRINT 31 SYMBOL TABLE BYTES EXCLUDING ATTRIBUTES: 1872
CONVERT SQLPRINT 32 -THERE WERE 3 MESSAGES FOR THIS PROGRAM.
CONVERT SQLPRINT 33 THERE WERE 0 MESSAGES SUPPRESSED BY THE FLAG OPTION.
CONVERT SQLPRINT 34 252152 BYTES OF STORAGE WERE USED BY THE PRECOMPILER.
CONVERT SQLPRINT 35 RETURN CODE IS 8
CONVERT SQLTERM 1 DSNH527I W DSNHOPTS THE PRECOMPILER ATTEMPTED TO USE THE DB2-SUPPLIED DSNHDECP MODULE
CONVERT SQLTERM 2 DSNH016I E DSNHPARS LINE 21 COL 13 "GLOBAL" REQUIRED
CONVERT SQLTERM 3 DSNH104I E DSNHPARS LINE 21 COL 13 ILLEGAL SYMBOL "CURSOR1". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: GLOBAL
CONVERT SQLTERM 4 CREATE PROCEDURE FDBMD.RETRIEVE_PROCEDURES_CURRENT(IN P_PLAN_TYPE VARCHAR(5))RESULT SETS 1 LANGUAGE SQL COLLID FODBCOLL WLM ENVIRO
CONVERT SQLTERM 5 NMENT WLMTENV4 RUN OPTIONS'NOTEST(ALL,*,,VADTCPIP&172.16.5.89:*)'P1 : BEGIN DECLARE GLOBAL TEMPORARY TABLE T(PLAN_TYPE VARCHAR(2)N
CONVERT SQLTERM 6 OT NULL,PROC_CODE VARCHAR(5)NOT NULL,SEQ_NBR INTEGER NOT NULL); INSERT INTO T SELECT PLAN_TYPE,PROC_CODE,MAX(SEQ_NBR)FROM FDBMD.PR
CONVERT SQLTERM 7 OCEDURE_ACTIVE WHERE PLAN_TYPE = P_PLAN_TYPE GROUP BY PLAN_TYPE,PROC_CODE ; DECLARE CURSOR1
CONVERT SQLTERM 8
CONVERT SQLTERM 9 SOURCE STATISTICS
CONVERT SQLTERM 10 SOURCE LINES READ: 32
CONVERT SQLTERM 11 NUMBER OF SYMBOLS: 12
CONVERT SQLTERM 12 SYMBOL TABLE BYTES EXCLUDING ATTRIBUTES: 1872
CONVERT SQLTERM 13 THERE WERE 3 MESSAGES FOR THIS PROGRAM.
CONVERT SQLTERM 14 THERE WERE 0 MESSAGES SUPPRESSED BY THE FLAG OPTION.
CONVERT SQLTERM 15 252152 BYTES OF STORAGE WERE USED BY THE PRECOMPILER.
CONVERT SQLTERM 16 RETURN CODE IS 8
DSNTPSMP CONSOLE 1 *** DB2 V8R1 SQL Procedure Processor DSNTPSMP 1.20 (PQ86898 2004-04-01)
DSNTPSMP CONSOLE 2 Language conversion warnings and/or errors, rc=8
DSNTPSMP CONSOLE 3 PSM precompile is not successful, cannot continue
DSNTPSMP Summary 1 Request: ALTER_REBUILD routine FDBMD.RETRIEVE_PROCEDURES_CURRENT did not complete.
Build utility function requested: ALTER_REBUILD
SYSPROC.DSNTPSMP - Returned +8
FDBMD.RETRIEVE_PROCEDURES_CURRENT - Build failed.
FDBMD.RETRIEVE_PROCEDURES_CURRENT - Roll back completed successfully.
Joined: 29 Nov 2002 Posts: 382 Topics: 27 Location: Chennai, India
Posted: Wed Mar 08, 2006 11:05 am Post subject:
NASCAR9,
Try this. After the GTT declare, wherever you are referencing the GTT table "T", prefix it with the qualifier - "Session".
Something like - INSERT INTO SESSION.T.....
Let me know how it goes.
Regards,
Manas _________________ There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)
Joined: 26 Nov 2002 Posts: 12380 Topics: 75 Location: San Jose
Posted: Wed Mar 08, 2006 11:09 am Post subject:
Susila,
nascar9,
Your SQL Statements Should on/after column 12. But looking at your messages, your sql statements are starting before col 12. Did you have NUM ON STD COB in your profile?
Now check your sql statements and make sure that they are starting on/after col 12
Joined: 29 Nov 2002 Posts: 382 Topics: 27 Location: Chennai, India
Posted: Wed Mar 08, 2006 11:27 am Post subject:
kolusu,
Nascar9 is using SQL procedures here which don't need to start after column 12. They are not COBOL stored procedures.
And I don't think Nascar9 is pre-compiling them on the mainframe either. He is using Development center to build the procedures.
Nascar9 - I have always found the development center not too compatible with DB2 on Z.OS if your ultimate intention is to run the stored procedures on DB2 for Z.OS. I would suggest coding and precompiling the SQL stored procedures on the mainframe and using the DSNHPC precompiler on the mainframe itself.
Regards,
Manas _________________ There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Wed Mar 08, 2006 11:42 am Post subject:
Manas,
I tried 'SESSION' with no luck. Same error.
The error is pointing to "CURSOR1". That seems to be the problem.
I'm having great luck with Development Center with SQL procedures. All are very easy to write (except this one) and perform very well. We are running DB2 V8.1.5 Z.OS. Also, all the latest patches have been applied to Development Center. _________________ Thanks,
NASCAR9
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Wed Mar 15, 2006 5:20 pm Post subject:
Working with the VB.NET programmer at work the problem has been solved. Here is how the procedure needed to be coded. The actual cursor1 never executes until the OPEN cursor1 statement.
Code:
CREATE PROCEDURE FDBMD.RETRIEVE_PROCEDURES_CURRENT ( IN P_PLAN_TYPE VARCHAR(5))
RESULT SETS 1
LANGUAGE SQL
COLLID FODBCOLL
WLM ENVIRONMENT WLMTENV4
RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&172.16.5.89:*)'
P1: BEGIN
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT P.PLAN_TYPE, P.PROC_CODE, P.PROVIDER, P.MODIFIER,
P.PLACE_OF_SERV, P.BILL_TYPE, P.DIAGNOSIS, P.RULESET_ID, 0, 0, 0, 0
FROM FDBMD.PROCEDURE_ACTIVE P
INNER JOIN SESSION.T as T ON P.PLAN_TYPE = T.PLAN_TYPE AND
P.PROC_CODE = T.PROC_CODE AND
P.SEQ_NBR = T.SEQ_NBR ;
DECLARE GLOBAL TEMPORARY TABLE SESSION.T
(PLAN_TYPE VARCHAR(5) NOT NULL,
PROC_CODE VARCHAR(5) NOT NULL,
SEQ_NBR INTEGER NOT NULL);
INSERT INTO SESSION.T
SELECT PLAN_TYPE, PROC_CODE, MAX(SEQ_NBR) as SEQ_NBR
FROM FDBMD.PROCEDURE_ACTIVE
WHERE PLAN_TYPE = P_PLAN_TYPE
GROUP BY PLAN_TYPE, PROC_CODE;
Joined: 29 Nov 2002 Posts: 382 Topics: 27 Location: Chennai, India
Posted: Thu Mar 16, 2006 9:44 am Post subject:
NASCAR9,
I am confused. I don't find any difference in the cursor declarations on your first post and your last post. The only thing that has changed is the qualification for you GTT with "SESSION". Something that I suggested earlier on and you said that you tried it without luck.
Regards,
Manas _________________ There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Thu Mar 16, 2006 10:37 am Post subject:
Manas Biswal wrote:
NASCAR9,
I am confused. I don't find any difference in the cursor declarations on your first post and your last post. The only thing that has changed is the qualification for you GTT with "SESSION". Something that I suggested earlier on and you said that you tried it without luck.
Regards,
Manas
I changed the DECLARE of the temp table and INSERT to after the DECLARE CURSOR. The CURSOR will not execute until the OPEN CURSOR.
Yes, I added 'SESSION' like you suggested; I also looked it up in the IBM doc. I appreciate your help, you sent me down the correct path. _________________ Thanks,
NASCAR9
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
