IBM Mainframe Forum

Hi ,

I was trying to create a table with the below , but i was getting an error as Illegal symbol. I have pasted the error message at the bottom of the page. Tried looking at the syntax but could'nt get any clue. Our DB2 version is V8.1. Could you please provide me your l guidance

syntax used to create table
CREATE TABLE RECON (
SECACCNO INTEGER NULL,
CUSTOMERNO VARCHAR(35) NULL,
ACCCALCORCCY VARCHAR(4) NULL,
OBJECTROWNO VARCHAR(16) NOT NULL,
OBJECTSUBROWNO VARCHAR(8) NOT NULL,
OBJECTPROCESSCODE VARCHAR(16) NULL,
OBJECTPROCESSCAT VARCHAR(4) NULL,
OBJECTPROCFLAG VARCHAR(1) NULL,
CLIENTINSTCODE VARCHAR(8) NULL ,
ACCCALCEXTREFID VARCHAR(35) NULL,
TAXRATE DECIMAL(96) NULL,
INCOMEPAYMAMT DECIMAL(232) NULL,
TAXAMT DECIMAL(232) NULL,
CROSSRATE DECIMAL(186) NULL,
ACCCALCFATCACAT VARCHAR(4) NULL,
ACCCALCDATE DATE NULL,
ACCCALCPAYMDATE DATE NULL,
ACCCALCOSDATE DATE NULL,
EXECUTIONTRADEDATE DATE NULL,
PAYMMATURITYDATE DATE NULL,
CASHPOSTINGDATE DATE NULL,
ACCCALCVALUEDATE DATE NULL,
REPORTINGREFDATE DATE NULL,
ACCCALCQTY DECIMAL(236) NULL,
ACCCALCQTYUNITCODE VARCHAR(4) NULL,
IRSINCOMETYPE VARCHAR(4) NULL,
FIIDENTNO VARCHAR(19) NULL,
FIFATCAIDENTSCHEMA VARCHAR(4) NULL,
FIFATCAINCOMECODE VARCHAR(4) NULL,
SECFATCACAT VARCHAR(4) NULL,
SECACCFATCACAT VARCHAR(4) NULL,
SECACCFULLNAME VARCHAR(120) NULL,
OBJECTLASTCHANGEDTST TIMESTAMP NULL,
OBJECTEXTPROCESSTST TIMESTAMP NULL ,
ACCCALCCANCEXTREFID VARCHAR(35) NULL,
FINPARTNERISSUERCNTRYCODE VARCHAR(4) NULL )
IN DB616771.TS001 ;


error message:
SQL ERROR DURING EXECUTE IMMEDIATE
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ",". SOME SYMBOLS THAT MIGHT BE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 119 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF' X'0000
INFORMATION

2 things
* you don't specify NULL , you specify NOT NULL or you specify nothing.
* decimal(93), decimal(232) ? seriously ?

the table definition is against all good sense,
read the manuals and the redbooks about the varchar quirks
somewhere it tells the number of chars for which a varchar is a waste of resources

a select on a varchar takes more resources that a select on a fixed length column

a a varchar(4) is certainly a waste, anyway the lower limit for varchar choice is pretty high
apart the varchar(120) I would make them all fixed some thinking about the varchar(35)

rereadig ... a varchar(1) is just a plain good judgement murder moreover with the null indicator.

wiser to review the whole shebang

I was able to create and Yes I made some changes in the value(VARCHAR/CHAR) which could Prevent hampering the resource utilisation.