Page 1 of 1

When REORG TABLESPACE getting ABEND=S213

PostPosted: Thu Mar 15, 2012 2:36 pm
by pavelbely
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:
//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

Re: When REORG TABLESPACE getting ABEND=S213

PostPosted: Thu Mar 15, 2012 3:05 pm
by enrico-sorichetti
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.

Re: When REORG TABLESPACE getting ABEND=S213

PostPosted: Thu Mar 15, 2012 3:14 pm
by Akatsukami
To expand on Dr. Sorichetti's post:
  1. Do not give a PDSE a disposition of MOD.
  2. What do you think you are accomplishing by directing SYSREC and SYSPUNCH to the same PDSE member?
  3. 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.

Re: When REORG TABLESPACE getting ABEND=S213

PostPosted: Thu Mar 15, 2012 5:43 pm
by pavelbely
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:
-- 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.
-- 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.

Re: When REORG TABLESPACE getting ABEND=S213

PostPosted: Thu Mar 15, 2012 7:57 pm
by Akatsukami
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?

Re: When REORG TABLESPACE getting ABEND=S213

PostPosted: Thu Mar 15, 2012 9:56 pm
by pavelbely
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.

Re: When REORG TABLESPACE getting ABEND=S213

PostPosted: Thu Mar 15, 2012 10:59 pm
by Akatsukami
You did not show any DDL for an index constraining NAME; that is probably why DB2 considers the definition to be incomplete.

Re: When REORG TABLESPACE getting ABEND=S213

PostPosted: Fri Mar 16, 2012 7:05 pm
by pavelbely
Thank you Akatsukami!

I created the following index and now everything's fine with TEST table indexes.
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!