|
View previous topic :: View next topic
|
| Author |
Message |
HABBIE
New User
Joined: 08 Jul 2014
Posts: 24
Location: INDIA
|
|
|
|
Hi,
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 DBNAME varchar(50);
DECLARE exitcode INTEGER DEFAULT 0;
DECLARE STMT1 varchar(1000);
DECLARE TRY_SERVER varchar(3000);
DECLARE TRY_SQLDB_METADATA varchar(4000);
DECLARE tableExists int;
DECLARE LOG_PROCESS VARCHAR(4000);
DECLARE JOB_ID INTEGER;
DECLARE LINE_NUM INTEGER;
DECLARE M_JOB_ID INTEGER;
DECLARE USR_MAP VARCHAR(4000);
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE TEXT VARCHAR(32672);
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 .
How to resolve this.
OS: Windows,
Db2 : 8 version
Code'd, for what it was worth |
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Check your brackets "(" and ")" match. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
as Bill and message is self telling what is wrong with this code.
You have missed ")" for this statement.
| Code: |
| (select '''||DBNAME||''' as DB_NAME, |
|
|
| Back to top |
|
|
HABBIE
New User
Joined: 08 Jul 2014
Posts: 24
Location: INDIA
|
|
|
|
thanks for the info. yes I made a mistake about the brace,
I have corrected the brace & now I m getting some other error:
CREATE PROCEDURE ()TRY
LANGUAGE SQL
FENCED
COLLID TEST
WLM ENVIRONMENT DB8GENV1
RUN OPTIONS 'NOTEST(NONE,*,*,*)'
P1: BEGIN
DECLARE DBNAME varchar(50);
DECLARE exitcode INTEGER DEFAULT 0;
DECLARE STMT1 varchar(1000);
DECLARE TRY_SERVER varchar(3000);
DECLARE TRY_SQLDB_METADATA varchar(4000);
DECLARE tableExists int;
DECLARE LOG_PROCESS VARCHAR(4000);
DECLARE JOB_ID INTEGER;
DECLARE LINE_NUM INTEGER;
DECLARE M_JOB_ID INTEGER;
DECLARE USR_MAP VARCHAR(4000);
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE TEXT VARCHAR(32672);
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. |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
| Try using the code tags. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
