Page 1 of 1

What to do with SQLCODE = -144

PostPosted: Wed Mar 09, 2011 2:18 am
by michel123
Hello,

I wanted to create DB2 objects in a batch, and I got SQL error -144 which leaves me very perplexed. I have the following message:

DSNTIAD - SAMPLE DYNAMIC SQL PROGRAM 2.0

CREATE STOGROUP PGSG001
VOLUMES (FAN003)
VCAT PG8997
Error during SQL EXECUTE IMMEDIATE
SQLCODE = -144 DSNT408I, ERROR: INVALID SECTION NUMBER 1
SQLSTATE = 58003 SQLSTATE DSNT418I RETURN CODE
DSNT415I SQLERRP = DSNXERT SQL PROCEDURE ERROR DETECT
DSNT416I SQLERRD = -215 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD X'FFFFFF29 = 'X'00000000' X'00000000 'X'FFFFFFFF' X'000 00000 'SQL DIAGNOSTIC
INFORMATION

with the following JCL:

006500 / / EXEC PGM = PH01S01 IKJEFT01, DYNAMNBR = 20, COND = (4, LT)
006600 / / DD SYSOUT =* SYSTSPRT
006700 / / DD * SYSTSIN
006800 DSN SYSTEM (XXXX)
006900 RUN PROGRAM (DSNTIAD) PLAN (DSNTIA81) -
007000 LIB ('DSN810.RUNLIB.LOAD')
007100 / / SYSPRINT DD SYSOUT =*
007200 / / DD SYSOUT =* SYSUDUMP
007300 / / SYSIN DD *
007400
007410 CREATE STOGROUP PGSG001
007,420 VOLUME (FAN003)
007430 VCAT PG8997;


Thank you for your help.

Re: What to do with SQLCODE = -144

PostPosted: Wed Mar 09, 2011 3:10 am
by dick scherrer
Hello,

Did you look up the -144 in the db2 documentation?
http://publibz.boulder.ibm.com/cgi-bin/ ... 0/2.1.6.44

You need to repost the jcl using copy/paste and the "Code" tag. The posted jcl has multiple errors . . . :?

Re: What to do with SQLCODE = -144

PostPosted: Wed Mar 09, 2011 6:03 pm
by michel123
Hello,

The explanation given on the IBM site is not very clear for me, I do not understand what may be the section

I'll post the JCL used :

//PG8997J JOB (12345678),PG8997,MSGCLASS=H,
//** RESTART=SELECT1,
// MSGLEVEL=(1,1),CLASS=A,NOTIFY=&SYSUID
//*
//JOBLIB DD DSN=DSN810.SDSNLOAD,DISP=SHR
//*********************************************************************
//* NOM = DEFVENTE
//*
//*********************************************************************
//*
//*********************************************************************
//* ASMCL PROC - ASSEMBLE AND LINKEDIT AN ASM PROGRAM
//*
//ASMCL PROC WSPC=500,MEM=TEMPNAME
//*
//* ASSEMBLE
//*
//ASM EXEC PGM=ASMA90,PARM='OBJECT,NODECK'
//SYSIN DD DSN=DSN810.SDSNSAMP(&MEM),
// DISP=SHR
//SYSLIB DD DSN=SYS1.MACLIB,DISP=SHR
// DD DSN=DSN810.SDSNMACS,DISP=SHR
// DD DSN=DSN810.SDSNSAMP,DISP=SHR
//SYSLIN DD DSN=&&LOADSET,DISP=(MOD,PASS),UNIT=SYSDA,
// SPACE=(800,(&WSPC,&WSPC)),DCB=(BLKSIZE=800)
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSUT1 DD UNIT=SYSDA,SPACE=(800,(&WSPC,&WSPC),,,ROUND)
//SYSUT2 DD UNIT=SYSDA,SPACE=(800,(&WSPC,&WSPC),,,ROUND)
//SYSUT3 DD UNIT=SYSDA,SPACE=(800,(&WSPC,&WSPC),,,ROUND)
//*
//* LINKEDIT IF THE ASSEMBLER
//* RETURN CODE IS 4 OR LESS
//*
//LKED EXEC PGM=IEWL,PARM='LIST,XREF,NCAL',
// COND=(4,LT,ASM)
//SYSLIN DD DSN=&&LOADSET,DISP=(OLD,DELETE)
//SYSLMOD DD DSN=DSN810.SDSNEXIT(&MEM),DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSUT1 DD UNIT=SYSDA,SPACE=(1024,(50,50))
//*
//ASMCL PEND
//*********************************************************************
//*
//* STEP 1: CREATE SAMPLE STORAGE GROUPS, TABLESPACES
//*
//PH01S01 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(XXXX)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA81) -
LIB('DSN810.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *

CREATE STOGROUP PGSG001
VOLUMES (FAN003)
VCAT PG8997;

CREATE DATABASE PGDB001
STOGROUP PGSG001
BUFFERPOOL BP0
CCSID EBCDIC;

CREATE TABLESPACE PGTS001
IN PGDB001
USING STOGROUP PGSG001
PRIQTY 10
SECQTY 10
ERASE NO
LOCKSIZE ANY LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC;

COMMIT ;

//*
//* STEP 2: CREATE SAMPLE TABLES, VIEWS
//*
//PH01S02 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(XXXX)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA81) -
LIB('DSN810.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *

CREATE TABLE ARTICLE
(NUMART CHAR (4) NOT NULL,
NOMART VARCHAR (25) NOT NULL,
PU DEC (7,2) NOT NULL,
COULEUR CHAR (10),
COMPOSE CHAR (4),
PRIMARY KEY (NUMART))
IN PGDB001.PGTS001
AUDIT NONE
CCSID EBCDIC;

CREATE UNIQUE INDEX XARTICLE
ON ARTICLE
(NUMART ASC)
USING STOGROUP PGSG001
PRIQTY 12
SECQTY 10
ERASE NO
BUFFERPOOL BP0
PCTFREE 0
FREEPAGE 0
CLUSTER
CLOSE NO;

COMMIT ;

CREATE TABLE LIGCOM
(NUMCOM CHAR (4) NOT NULL,
NUMART CHAR (4) NOT NULL,
QTE SMALLINT NOT NULL,
REMISE DEC (5,2),
PRIMARY KEY (NUMCOM, NUMART) ,
FOREIGN KEY LIGCOMCO (NUMCOM)
REFERENCES COMMANDE
ON DELETE CASCADE ,
FOREIGN KEY LIGCOMAR (NUMART)
REFERENCES ARTICLE
ON DELETE RESTRICT )
IN PGDB001.PGTS001
AUDIT NONE
CCSID EBCDIC;

CREATE UNIQUE INDEX XLIGCOM
ON LIGCOM
(NUMCOM ASC, NUMART)
USING STOGROUP PGSG001
PRIQTY 12
SECQTY 10
ERASE NO
BUFFERPOOL BP0
PCTFREE 0
FREEPAGE 0
CLUSTER
CLOSE NO;

COMMIT ;

CREATE TABLE COMMANDE
(NUMCOM CHAR (4) NOT NULL,
NOMART VARCHAR (25) NOT NULL,
DATREC DATE NOT NULL,
DATLIV DATE ,
NUMCLI CHAR (4) NOT NULL,
PRIMARY KEY (NUMCOM) ,
FOREIGN KEY COMCLI (NUMCLI)
REFERENCES CLIENT
ON DELETE RESTRICT)
IN PGDB001.PGTS001
AUDIT NONE
CCSID EBCDIC;

CREATE UNIQUE INDEX XCOMMANDE
ON COMMANDE
(NUMCOM ASC)
USING STOGROUP PGSG001
PRIQTY 12
SECQTY 10
ERASE NO
BUFFERPOOL BP0
PCTFREE 0
FREEPAGE 0
CLUSTER
CLOSE NO;

COMMIT ;

CREATE TABLE CLIENT
(NUMCLI CHAR (4) NOT NULL,
NOMCLI VARCHAR (25) NOT NULL,
ADRCLI VARCHAR (25) NOT NULL,
PRIMARY KEY (NUMCLI))
IN PGDB001.PGTS001
AUDIT NONE
CCSID EBCDIC;

CREATE UNIQUE INDEX XCLIENT
ON CLIENT
(NUMCLI ASC)
USING STOGROUP PGSG001
PRIQTY 12
SECQTY 10
ERASE NO
BUFFERPOOL BP0
PCTFREE 0
FREEPAGE 0
CLUSTER
CLOSE NO;
COMMIT ;

//*
//* STEP 6: GRANT AUTHORITY, CREATE SYNONYMS
//*
//PH01S06 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(XXXX)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA81) -
LIB('DSN810.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
GRANT USE OF STOGROUP PGSG001 TO PUBLIC;
GRANT DBADM ON DATABASE PGDB001 TO PUBLIC;
GRANT USE OF TABLESPACE PGTS001 TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE PG8997.ARTICLE
TO PUBLIC AT ALL LOCATIONS;
//*
//*
//* STEP 23: ATTEMPT INSERT INTO THE ARTICLE TABLE
//*
//PH01S23 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(XXXX)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA81) PARM('RC0') -
LIB('DSN810.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *

INSERT INTO ARTICLE
(NUMART,NOMART,PU,COULEUR,COMPOSE)
VALUES ('A001','GARE',100.00,NULL,'A010');
INSERT INTO ARTICLE
(NUMART,NOMART,PU,COULEUR,COMPOSE)
VALUES ('A003','TGV',240.50,'ORANGE','A010');
INSERT INTO ARTICLE
(NUMART,NOMART,PU,COULEUR,COMPOSE)
VALUES ('A005','RAILS',160.90,NULL,'A010');
INSERT INTO ARTICLE
(NUMART,NOMART,PU,COULEUR,COMPOSE)
VALUES ('A010','COFFRET SNCF',450.00,NULL,NULL);
INSERT INTO ARTICLE
(NUMART,NOMART,PU,COULEUR,COMPOSE)
VALUES ('A036','CASQUE',95.50,'ROUGE','A040');
INSERT INTO ARTICLE
(NUMART,NOMART,PU,COULEUR,COMPOSE)
VALUES ('A037','GENOUILLERE',95.50,'VERT','A040');
INSERT INTO ARTICLE
(NUMART,NOMART,PU,COULEUR,COMPOSE)
VALUES ('A038','SKATE',170.00,'JAUNE','A050');
INSERT INTO ARTICLE
(NUMART,NOMART,PU,COULEUR,COMPOSE)
VALUES ('A040','COFFRET SECURITE',100.00,NULL,'A050');
INSERT INTO ARTICLE
(NUMART,NOMART,PU,COULEUR,COMPOSE)
VALUES ('A050','GARE',100.00,NULL,NULL);
INSERT INTO ARTICLE
(NUMART,NOMART,PU,COULEUR,COMPOSE)
VALUES ('A060','GARE',100.00,'ROUGE',NULL);
INSERT INTO ARTICLE
(NUMART,NOMART,PU,COULEUR,COMPOSE)
VALUES ('A070','GARE',100.00,'BLANC',NULL);
COMMIT;

//*

Re: What to do with SQLCODE = -144

PostPosted: Mon Mar 14, 2011 7:58 pm
by michel123
Hello,

in fact, I managed to execute my functions DB2 by taking the DSNTEP2 utility with the DSNTEP81 plan DSNTEP81, instead of DSNTIAD.

Re: What to do with SQLCODE = -144

PostPosted: Tue Mar 15, 2011 3:08 am
by dick scherrer
Good to hear it is working - thank you for letting us know :)

d