Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Illegal symbol syntax error

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
HABBIE

New User


Joined: 08 Jul 2014
Posts: 24
Location: INDIA

PostPosted: Wed Aug 26, 2015 5:58 pm    Post subject: Illegal symbol syntax error
Reply with quote

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
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Wed Aug 26, 2015 7:56 pm    Post subject: Reply to: Illegal symbol syntax error
Reply with quote

Check your brackets "(" and ")" match.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Aug 26, 2015 11:45 pm    Post subject:
Reply with quote

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
View user's profile Send private message
HABBIE

New User


Joined: 08 Jul 2014
Posts: 24
Location: INDIA

PostPosted: Thu Aug 27, 2015 5:09 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Thu Aug 27, 2015 8:27 pm    Post subject:
Reply with quote

Try using the code tags.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Aug 27, 2015 8:42 pm    Post subject:
Reply with quote

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.

Hint-
https://www-304.ibm.com/support/knowledgecenter/#!/SSEPEK_10.0.0/com.ibm.db2z10.doc.codes/src/tpc/n311.dita
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts S922 Error yuvan ABENDS & Debugging 3 Fri Dec 02, 2016 6:58 pm
No new posts Invoke Webservice Fails with DFHPI100... divated CICS 2 Thu Nov 24, 2016 5:57 pm
No new posts Error during restore rename archanamuthukrishnan All Other Mainframe Topics 2 Fri Oct 14, 2016 3:30 pm
No new posts Error IEC161I 052(009,XXXRS00)-084 wh... amitc23 JCL & VSAM 10 Wed Oct 12, 2016 5:00 pm
No new posts IDMS/DC-COBOL program - SNAP error wh... rakeshsekar1987 IDMS/ADSO 5 Tue Sep 13, 2016 8:28 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us