I am new to stored procedures on db2 Z/OS.
I have connected to the Mainframe machine via IBM data studio to access Db2 & to create native plsql stored procedures.Sorry to post my code, but I have given everything in a proper way. Able to compile but while running I am getting the below error:
Code:
CREATE PROCEDURE ()TRY
LANGUAGE SQL
FENCED
COLLID TEST
WLM ENVIRONMENT DB8GENV1
RUN OPTIONS 'NOTEST(NONE,*,*,*)'
P1: BEGIN
DECLARE mycur CURSOR WITH HOLD FOR
select SCHEMA_NAME from DBLIST;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET exitcode = 1;
OPEN mycur;
FETCH FROM mycur INTO DBNAME;
wloop1:
WHILE (SQLCODE = 0)
DO
SET TRY_SQLDB_METADATA = 'INSERT INTO TRY1.TRY_SQLDB_METADATA
(DB_NAME,TABLE_NAME,COLUMN_NAME,S_COLUMN_TYPE,MAX_ LENGTH,IS_NULLABLE)
(select '''||DBNAME||''' as DB_NAME,
TAB.NAME as TABLE_NAME,
COL.NAME as COLUMN_NAME,
COL.COLTYPE as S_COLUMN_TYPE,
COL.LENGTH as MAX_LENGTH,
COL.NULLS as IS_NULLABLE
from SYSIBM.SYSTABLES TAB,SYSIBM.SYSCOLUMNS COL
where TAB.CREATOR = '''||DBNAME||''' and TAB.NAME not like ''SYS%''
and TAB.CREATOR not like ''SYS%'' and
TAB.type = ''T'' and COL.TBCREATOR=TAB.CREATOR';
execute immediate TRY_SQLDB_METADATA;
SET exitcode = 0 ;
FETCH FROM mycur INTO DBNAME;
IF (exitcode = 1)
THEN
LEAVE wloop1;
END IF;
END WHILE;
CLOSE mycur;
END P1
Error:
Code:
Run: TRY1.TRY()
{? = call TRY()}
ILLEGAL SYMBOL "<END-OF-STATEMENT>". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ) UNION EXCEPT. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.17.30
Run of routine failed.
- Roll back completed successfully.
what does this error. I never faced this while I m working on DB2LUW .
DECLARE mycur CURSOR WITH HOLD FOR
select SCHEMA_NAME from DBLIST;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET exitcode = 1;
OPEN mycur;
FETCH FROM mycur INTO DBNAME;
wloop1:
WHILE (SQLCODE = 0)
DO
SET TRY_SQLDB_METADATA = 'INSERT INTO TRY1.TRY_SQLDB_METADATA
(DB_NAME,TABLE_NAME,COLUMN_NAME,S_COLUMN_TYPE,MAX_ LENGTH,IS_NULLABLE)
(select '''||DBNAME||''' as DB_NAME,
TAB.NAME as TABLE_NAME,
COL.NAME as COLUMN_NAME,
COL.COLTYPE as S_COLUMN_TYPE,
COL.LENGTH as MAX_LENGTH,
COL.NULLS as IS_NULLABLE
from SYSIBM.SYSTABLES TAB,SYSIBM.SYSCOLUMNS COL
where TAB.CREATOR = '''||DBNAME||''' and TAB.NAME not like ''SYS%''
and TAB.CREATOR not like ''SYS%'' and
TAB.type = ''T'' and COL.TBCREATOR=TAB.CREATOR)';
execute immediate TRY_SQLDB_METADATA;
SET exitcode = 0 ;
FETCH FROM mycur INTO DBNAME;
IF (exitcode = 1)
THEN
LEAVE wloop1;
END IF;
END WHILE;
CLOSE mycur;
END P1
Error:
THE LENGTH OF INPUT HOST VARIABLE NUMBER 001 IS NEGATIVE OR GREATER THAN THE MAXIMUM. SQLCODE=-311, SQLSTATE=22501, DRIVER=4.17.30
Run of routine failed.
- Roll back completed successfully.
I have given all the values correctly , don't know what I have to change here. Can Someone plz guide me here.
Anyone here could might easily help but you will not get learned anything, so please search on Google or try to understand what the error is all about and give a try..
If you still don't find any answers by yourself then please post back of what all you tried and then I am sure someone will provide you a solution.
