IBM Mainframe Forum

by **pavelbely** » Thu Mar 15, 2012 2:36 pm

Hello everybody,

I failed to run REORG TABLESPACE, getting ABEND=S213

1 J E S 2 J O B L O G -- S Y S T E M M V S 1 -- N O D E T S T M V S 0 1
0
16.41.11 JOB00170 ---- WEDNESDAY, 14 MAR 2012 ----
16.41.11 JOB00170 ICH70001I PBELY LAST ACCESS AT 16:39:51 ON WEDNESDAY, MARCH 14, 2012
16.41.11 JOB00170 $HASP373 DSNURGT STARTED - INIT 1 - CLASS A - SYS MVS1
16.41.11 JOB00170 IEF403I DSNURGT - STARTED - TIME=16.41.11
16.41.12 JOB00170 IEC143I 213-3C,IGG0191A,DSNURGT,DSNUPROC,SYSREC,01CE,DMTP03,PBELY.UTIL.SYSIN(TEMP)
16.41.13 JOB00170 IEF450I DSNURGT DSNUPROC UTIL - ABEND=S213 U0000 REASON=0000003C 778

I created JCL to call REORG TABLESPACE through DB2I Utilities.
Here it is:

Select all

//DSNURGT JOB YOUR_JOB_CARD_PARAMETERS,
// USER=USER_ID,PASSWORD=USER_PASSWORD
/*ROUTE PRINT ROUTING_INFORMATION
//UTIL EXEC DSNUPROC,SYSTEM=DSNB,UID='TEMP',UTPROC=''
//*
//**********************************************
//*
//* GENERATING JCL FOR THE REORG TABLESPACE UTILITY
//* DATE: 03/14/12 TIME: 16:39:12
//*
//**********************************************
//*
//DSNUPROC.SORTWK01 DD DSN=PBELY.SORTWK01,
// DISP=(MOD,DELETE,CATLG)
//DSNUPROC.SORTWK02 DD DSN=PBELY.SORTWK02,
// DISP=(MOD,DELETE,CATLG)
//DSNUPROC.SORTWK03 DD DSN=PBELY.SORTWK03,
// DISP=(MOD,DELETE,CATLG)
//DSNUPROC.SORTWK04 DD DSN=PBELY.SORTWK04,
// DISP=(MOD,DELETE,CATLG)
//DSNUPROC.SYSREC DD DSN=PBELY.UTIL.SYSIN(TEMP),
// DISP=(MOD,CATLG)
//DSNUPROC.SYSPUNCH DD DSN=PBELY.UTIL.SYSIN(TEMP),
// DISP=(MOD,CATLG)
//DSNUPROC.SYSUT1 DD DSN=PBELY.SYSUT1,
// DISP=(MOD,DELETE,CATLG)
//DSNUPROC.SORTOUT DD DSN=PBELY.SORTOUT,
// DISP=(MOD,DELETE,CATLG)
//DSNUPROC.SYSIN DD *
REORG TABLESPACE DB2CERT.CERTTS 00010002
//

The deeper problem is that an index I created is inactive.
Firstly I created the database and all tables with indexes but for that TEST table.
Later I created the index for it, but it didn't start working.

SELECT PBELY.TEST_TAKEN.NUMBER,CID,TCID
FROM PBELY.TEST_TAKEN,PBELY.TEST
WHERE CID='111';
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -540, ERROR: THE DEFINITION OF TABLE PBELY.TEST IS
INCOMPLETE BECAUSE IT LACKS A PRIMARY INDEX OR A REQUIRED UNIQUE INDEX
DSNT418I SQLSTATE = 57001 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOTL SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -550 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFDDA' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

I hope running REORG TABLESPACE will help to get index for my table TEST working.
I had successfully run REORG INDEX for it but it didn't help.
I had dropped and recreated the database and all the tables but it didn't help neither.

Please help

by **enrico-sorichetti** » Thu Mar 15, 2012 3:05 pm

the abend 213 is not related to DB2, is a standard abend for conflicting dcb attributes
see the dsname in the IEC143I message

3C
An OPEN macro instruction was issued with a RECFM value that specified a format different from that specified by the format-1 DSCB, and the data set was a PDSE or compressed data set.

by **Akatsukami** » Thu Mar 15, 2012 3:14 pm

To expand on Dr. Sorichetti's post:

Do not give a PDSE a disposition of MOD.
What do you think you are accomplishing by directing SYSREC and SYSPUNCH to the same PDSE member?
Show the DDL for creating the index(es) on your table. Do not re-type it; copy and paste it, enclosing it in Code tags.

by **pavelbely** » Thu Mar 15, 2012 5:43 pm

Thank you for your answers.

To Akatsukami:
1. ok. Corrected from PDSE to empty PS dataset. REORG TABLESPACE had run fine, but it didn't help.
2. My wrong. I rechecked it in Utility Guide Refrerence, these are used for absolutely different purposes.
3. This is DDL that I ran first. It creates database with tables and indexes but for index for TEST table:

Select all

-- CREATE STOGROUP
CREATE STOGROUP CERTSTG
VOLUMES(DMTP01,DMTP02) VCAT PBELY;

-- CREATE DATABASE
CREATE DATABASE DB2CERT
STOGROUP CERTSTG
BUFFERPOOL BP1
INDEXBP BP2;

-- CREATE SEGMENTED TABLESPACE
CREATE TABLESPACE CERTTS
IN DB2CERT
USING STOGROUP CERTSTG
PRIQTY 52
SECQTY 20
ERASE NO
LOCKSIZE PAGE
BUFFERPOOL BP1
CLOSE YES
SEGSIZE 32;

-- CREATE PARTITIONED TABLESPACE WITH 2 PARTITIONS
CREATE TABLESPACE CERTTSPT
IN DB2CERT
USING STOGROUP CERTSTG
PRIQTY 100
SECQTY 120
ERASE NO
NUMPARTS 2
(PART 1
COMPRESS YES,
PART 2
FREEPAGE 20)
LOCKSIZE PAGE
CLOSE NO;

-- CREATE LOB TABLESPACE
CREATE LOB TABLESPACE CERTPIC
IN DB2CERT
USING STOGROUP CERTSTG
PRIQTY 3200
SECQTY 1600
LOCKSIZE LOB
GBPCACHE SYSTEM
LOG NO
CLOSE NO;

-- CREATE DISTINCT DATA TYPES
CREATE DISTINCT TYPE PBELY.PHONE AS CHAR(10) WITH COMPARISONS;
CREATE DISTINCT TYPE PBELY.BITMAP AS BLOB(1M);
CREATE DISTINCT TYPE PBELY.MINUTES AS SMALLINT WITH COMPARISONS;

-- CREATE PARENT TABLE
CREATE TABLE PBELY.CANDIDATE
(CID CHAR(6) NOT NULL,
LNAME VARCHAR(30) NOT NULL,
FNAME VARCHAR(30) NOT NULL,
INITIAL CHAR(1),
HPHONE PHONE,
WPHONE PHONE,
STREETNO VARCHAR(8),
STREETNAME VARCHAR(20) NOT NULL,
CITY VARCHAR(30) NOT NULL,
STATE VARCHAR(30) NOT NULL,
CODE CHAR(6) NOT NULL,
COUNTRY VARCHAR(20) NOT NULL,
CERT_DBA CHAR(1) NOT NULL WITH DEFAULT,
CERT_APP CHAR(1) NOT NULL WITH DEFAULT,
PHOTO BITMAP,
PRIMARY KEY(CID))
IN DB2CERT.CERTTS;

-- CREATE UNIQUE CLUSTERING INDEX
CREATE UNIQUE INDEX PBELY.CANDIX
ON PBELY.CANDIDATE
(CID ASC)
USING STOGROUP CERTSTG
PRIQTY 512
SECQTY 64
ERASE NO
CLUSTER
BUFFERPOOL BP2
CLOSE YES;

-- CREATE PARENT TABLE WITH CHECK CONSTRAINT
CREATE TABLE PBELY.TEST
(NUMBER CHAR(6) NOT NULL,
NAME VARCHAR(50) NOT NULL,
TYPE CHAR(1) NOT NULL,
CUT_SCORE DECIMAL(6,2) NOT NULL,
LENGTH MINUTES NOT NULL,
TOTALTAKEN SMALLINT NOT NULL WITH DEFAULT,
TOTALPASSED SMALLINT NOT NULL WITH DEFAULT,
CONSTRAINT UNIQUE_TEST_NUM PRIMARY KEY (NUMBER),
CONSTRAINT UNIQUE_TEST_NAME UNIQUE (NAME),
CONSTRAINT TEST_TYPE CHECK (TYPE IN ('P','B')))
IN DB2CERT.CERTTS;

-- CREATE TABLE WITH PARITIONING
CREATE TABLE PBELY.TEST_CENTER
(TCID CHAR(6) NOT NULL,
NAME VARCHAR(40) NOT NULL,
STEETNO VARCHAR(8) NOT NULL,
STREETNAME VARCHAR(20) NOT NULL,
CITY VARCHAR(30) NOT NULL,
PROV_STATE VARCHAR(30) NOT NULL,
COUNTRY VARCHAR(20) NOT NULL,
CODE CHAR(6) NOT NULL,
TYPE CHAR(1) NOT NULL,
PHONE PHONE NOT NULL,
NOSEATS SMALLINT NOT NULL,
PRIMARY KEY (TCID))
IN DB2CERT.CERTTSPT
PARTITION BY (TCID)
(PARTITION 1 ENDING AT ('300'),
PARTITION 2 ENDING AT ('500'));

-- CREATE UNIQUE INDEX
CREATE UNIQUE INDEX PBELY.TESTCNTX
ON PBELY.TEST_CENTER
(TCID ASC)
USING STOGROUP CERTSTG
PRIQTY 512
SECQTY 64
ERASE NO
CLUSTER
BUFFERPOOL BP2
CLOSE YES;

-- CREATE NON-PARTITIONING INDEX WITH PIECES
CREATE UNIQUE INDEX PBELY.TESTCN2X
ON PBELY.TEST_CENTER
(CODE ASC)
USING STOGROUP CERTSTG
PIECESIZE 512K;

-- CREATE DEPENDENT TABLE
CREATE TABLE PBELY.TEST_TAKEN
( CID CHAR(6) NOT NULL,
TCID CHAR(6) NOT NULL,
NUMBER CHAR(6) NOT NULL,
DATE_TAKEN DATE NOT NULL WITH DEFAULT,
START_TIME TIME WITH DEFAULT,
FINISH_TIME TIME WITH DEFAULT,
SCORE DECIMAL(6,2),
PASS_FAIL CHAR(1),
SEAT_NO CHAR(2) NOT NULL,
CONSTRAINT NUMBER_CONST
PRIMARY KEY (TCID,CID,DATE_TAKEN),
FOREIGN KEY (CID)
REFERENCES CANDIDATE(CID) ON DELETE CASCADE,
FOREIGN KEY (TCID)
REFERENCES TEST_CENTER(TCID) ON DELETE CASCADE,
FOREIGN KEY (NUMBER)
REFERENCES TEST(NUMBER) ON DELETE RESTRICT)
IN DB2CERT.CERTTS;

CREATE UNIQUE INDEX PBELY.TTAKENIX
ON PBELY.TEST_TAKEN
(TCID,CID,DATE_TAKEN ASC)
USING STOGROUP CERTSTG
PRIQTY 512
SECQTY 64
ERASE NO
CLUSTER
BUFFERPOOL BP2
CLOSE YES;

-- CREATE AUXILARY TABLE
CREATE AUX TABLE PBELY.CAND_PHOTO
IN DB2CERT.CERTPIC
STORES PBELY.CANDIDATE
COLUMN PHOTO;

-- CREATE AUXILARY INDEX
CREATE UNIQUE INDEX PBELY.PHOTOIX
ON PBELY.CAND_PHOTO
COPY YES;

Then I created index for TEST, but it's not working.

Select all

-- CREATE UNIQUE CLUSTERING INDEX
CREATE UNIQUE INDEX PBELY.TESTNUIX
ON PBELY.TEST
(NUMBER ASC)
USING STOGROUP CERTSTG
PRIQTY 512
SECQTY 64
ERASE YES
CLUSTER
BUFFERPOOL BP2
CLOSE YES;

Thank you for your help, REORG TABLESPACE successfully run.
But it hadn't helped to activate the index for TEST table.
Now I wonder how to clean all the mess and recreate the database entirely.

by **Akatsukami** » Thu Mar 15, 2012 7:57 pm

You can always just execute DROP DATABASE and all dependent objects (table spaces, tables, indexes, etc.) will also be dropped.

When you write "the index is not working", do you mean that there is an error with the creation of the index, or with a load of data into the indexed table?

by **pavelbely** » Thu Mar 15, 2012 9:56 pm

When you write "the index is not working", do you mean that there is an error with the creation of the index, or with a load of data into the indexed table?

Right. I successfully created the index for TEST table.
But then I can't run any DML statements against this table, I always get error.

SELECT PBELY.TEST_TAKEN.NUMBER,CID,TCID
FROM PBELY.TEST_TAKEN,PBELY.TEST
WHERE CID='111';
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -540, ERROR: THE DEFINITION OF TABLE PBELY.TEST IS
INCOMPLETE BECAUSE IT LACKS A PRIMARY INDEX OR A REQUIRED UNIQUE INDEX
DSNT418I SQLSTATE = 57001 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOTL SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -550 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFDDA' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

You can always just execute DROP DATABASE and all dependent objects (table spaces, tables, indexes, etc.) will also be dropped.

I tried dropping the whole database and droppping each element one by one - still the same problem.

by **Akatsukami** » Thu Mar 15, 2012 10:59 pm

You did not show any DDL for an index constraining NAME; that is probably why DB2 considers the definition to be incomplete.

by **pavelbely** » Fri Mar 16, 2012 7:05 pm

Thank you Akatsukami!

I created the following index and now everything's fine with TEST table indexes.

Select all

CREATE UNIQUE INDEX PBELY.TESTNAIX
ON PBELY.TEST
(NAME ASC)
USING STOGROUP CERTSTG
PRIQTY 512
SECQTY 64
ERASE YES
BUFFERPOOL BP2
CLOSE YES;

I thought just 1 index on a primary key constraint would be enough.
But now I know I need to create index for each unique or primary constraint.

Thank you for your help!

IBM Mainframe Forum

When REORG TABLESPACE getting ABEND=S213

When REORG TABLESPACE getting ABEND=S213

Re: When REORG TABLESPACE getting ABEND=S213

Re: When REORG TABLESPACE getting ABEND=S213

Re: When REORG TABLESPACE getting ABEND=S213

Re: When REORG TABLESPACE getting ABEND=S213

Re: When REORG TABLESPACE getting ABEND=S213

Re: When REORG TABLESPACE getting ABEND=S213

Re: When REORG TABLESPACE getting ABEND=S213