By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,457 Members | 1,274 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,457 IT Pros & Developers. It's quick & easy.

Database marked bad.

P: n/a
Hello.

W2k, db2 wse v8.1.4.
Here is ddl that creates small test database (3 tables and 1 view).
(i can't attach zip file with ixf files and loading script - it has ~ 1.3MB
size, but if anybody want to get it i can download it on your site, mail me
to __mark(at)mail.ru).
---
CREATE DATABASE TEST ON C:;

CONNECT TO TEST;

CREATE REGULAR TABLESPACE BATCHSPACE PAGESIZE 4096 MANAGED BY DATABASE
USING (FILE 'C:\TEST\CONT1' 5000) EXTENTSIZE 32 PREFETCHSIZE 32
BUFFERPOOL IBMDEFAULTBP;
CREATE TABLE BAT.TASKLOG
(TASKID INTEGER NOT NULL DEFAULT 0,
STARTID INTEGER NOT NULL,
SCHEDID INTEGER NOT NULL,
PROGID CHARACTER(8),
PROGKIND DECIMAL(1, 0) NOT NULL,
USERID CHARACTER(8) DEFAULT USER,
STARTSTAMP TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
ENDSTAMP TIMESTAMP DEFAULT CURRENT TIMESTAMP,
RC INTEGER DEFAULT 0
) IN BATCHSPACE;
CREATE TABLE BAT.TASKS
(TASKID INTEGER NOT NULL,
MASTERID INTEGER NOT NULL,
STARTID INTEGER,
TASKSTATE SMALLINT NOT NULL,
PROGID CHARACTER(8),
ONWKIND DECIMAL(1, 0) NOT NULL DEFAULT 0,
BKPROGID CHARACTER(8),
BKWKIND DECIMAL(1, 0) NOT NULL DEFAULT 1,
MAXRC INTEGER NOT NULL DEFAULT 0,
DISCLASS SMALLINT,
RESTARTS DECIMAL(1, 0) NOT NULL DEFAULT 0,
TASKNAME VARCHAR(100) NOT NULL DEFAULT '',
TASKNICK VARCHAR(20) NOT NULL DEFAULT '',
UPDATED TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
OPCODE SMALLINT NOT NULL DEFAULT 0
) IN BATCHSPACE;

CREATE UNIQUE INDEX BAT.TASKS1
ON BAT.TASKS
(MASTERID ASC,
TASKID ASC,
STARTID ASC,
TASKSTATE ASC
);

ALTER TABLE BAT.TASKLOG
ADD CONSTRAINT TASKLOG1 PRIMARY KEY
(TASKID,
STARTID
);

ALTER TABLE BAT.TASKS
ADD CONSTRAINT TASKS_PK PRIMARY KEY
(TASKID
);

ALTER TABLE BAT.TASKS
ADD CONSTRAINT TASKS_AK UNIQUE
(TASKID,
MASTERID
);

CREATE VIEW BAT.COMPOUNDS
(COMPONENT,
ENCLOSING,
DISTANCE
)
AS WITH COMPOUNDS1 (COMPONENT, ENCLOSING, DISTANCE) AS (
SELECT TASKID, MASTERID, 1
FROM BAT.TASKS
WHERE TASKID<>MASTERID
UNION ALL
SELECT A.TASKID MASTERID, B.ENCLOSING, B.DISTANCE+1
FROM BAT.TASKS A, COMPOUNDS1 B
WHERE A.MASTERID = B.COMPONENT
AND DISTANCE<10000 )
SELECT COMPONENT, ENCLOSING, DISTANCE
FROM COMPOUNDS1;

ALTER TABLE BAT.TASKS
ADD CONSTRAINT MASTERID FOREIGN KEY
(MASTERID
)
REFERENCES BAT.TASKS
(TASKID
)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

ALTER TABLE BAT.TASKS
ADD CONSTRAINT STARTID FOREIGN KEY
(TASKID,
STARTID
)
REFERENCES BAT.TASKLOG
(TASKID,
STARTID
)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

ALTER TABLE BAT.TASKS
ADD CONSTRAINT BACKWARDKIND CHECK
(BKWKIND=1);

ALTER TABLE BAT.TASKS
ADD CONSTRAINT ONWARDKIND CHECK
(ONWKIND=0);

ALTER TABLE BAT.TASKS
ADD CONSTRAINT RESTARTS CHECK
(RESTARTS in (0,1));

CREATE TABLE BAT.PROGPARMS
(PROGID CHARACTER(8) NOT NULL,
PARMNAME VARCHAR(18) NOT NULL,
DEFAULT VARCHAR(250) NOT NULL DEFAULT '',
MANDATORY DECIMAL(1, 0) NOT NULL DEFAULT 0,
REMARKS VARCHAR(250) NOT NULL DEFAULT '',
UPDATED TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
OPCODE SMALLINT NOT NULL DEFAULT 0
) IN BATCHSPACE;

ALTER TABLE BAT.PROGPARMS
ADD CONSTRAINT PROGPARMS_PK PRIMARY KEY
(PROGID,
PARMNAME
);
---
Having
42710 rows in BAT.TASKLOG,
33008 rows in BAT.TASKS,
706 rows in BAT.PROGPARMS,
after next command database is marked bad:
---
with allprogs(progid) as (select distinct t.progid from bat.compounds
c, bat.tasks t where c.component=t.taskid and c.enclosing=39592
union all select progid from bat.tasks where taskid=39592)
select p.parmname, p.progid, p.remarks
from allprogs a, bat.progparms p
where a.progid=p.progid and p.parmname not like '@%' order by p.parmname,
p.progid
---
This is extract from our production database, and we havn't seen such crash
before on db2 v6.1, v7.2, v8.1.0-2.
This crash appeared after applying FP3 or FP4.
I also tried db2 ese v8.1.3 with the same result.
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Since you're on the most recent fixpack, your best bet would be to
contact service...

Mark Barinstein wrote:
Hello.

W2k, db2 wse v8.1.4.
Here is ddl that creates small test database (3 tables and 1 view).
(i can't attach zip file with ixf files and loading script - it has ~ 1.3MB
size, but if anybody want to get it i can download it on your site, mail me
to __mark(at)mail.ru).
---
CREATE DATABASE TEST ON C:;

CONNECT TO TEST;

CREATE REGULAR TABLESPACE BATCHSPACE PAGESIZE 4096 MANAGED BY DATABASE
USING (FILE 'C:\TEST\CONT1' 5000) EXTENTSIZE 32 PREFETCHSIZE 32
BUFFERPOOL IBMDEFAULTBP;
CREATE TABLE BAT.TASKLOG
(TASKID INTEGER NOT NULL DEFAULT 0,
STARTID INTEGER NOT NULL,
SCHEDID INTEGER NOT NULL,
PROGID CHARACTER(8),
PROGKIND DECIMAL(1, 0) NOT NULL,
USERID CHARACTER(8) DEFAULT USER,
STARTSTAMP TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
ENDSTAMP TIMESTAMP DEFAULT CURRENT TIMESTAMP,
RC INTEGER DEFAULT 0
) IN BATCHSPACE;
CREATE TABLE BAT.TASKS
(TASKID INTEGER NOT NULL,
MASTERID INTEGER NOT NULL,
STARTID INTEGER,
TASKSTATE SMALLINT NOT NULL,
PROGID CHARACTER(8),
ONWKIND DECIMAL(1, 0) NOT NULL DEFAULT 0,
BKPROGID CHARACTER(8),
BKWKIND DECIMAL(1, 0) NOT NULL DEFAULT 1,
MAXRC INTEGER NOT NULL DEFAULT 0,
DISCLASS SMALLINT,
RESTARTS DECIMAL(1, 0) NOT NULL DEFAULT 0,
TASKNAME VARCHAR(100) NOT NULL DEFAULT '',
TASKNICK VARCHAR(20) NOT NULL DEFAULT '',
UPDATED TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
OPCODE SMALLINT NOT NULL DEFAULT 0
) IN BATCHSPACE;

CREATE UNIQUE INDEX BAT.TASKS1
ON BAT.TASKS
(MASTERID ASC,
TASKID ASC,
STARTID ASC,
TASKSTATE ASC
);

ALTER TABLE BAT.TASKLOG
ADD CONSTRAINT TASKLOG1 PRIMARY KEY
(TASKID,
STARTID
);

ALTER TABLE BAT.TASKS
ADD CONSTRAINT TASKS_PK PRIMARY KEY
(TASKID
);

ALTER TABLE BAT.TASKS
ADD CONSTRAINT TASKS_AK UNIQUE
(TASKID,
MASTERID
);

CREATE VIEW BAT.COMPOUNDS
(COMPONENT,
ENCLOSING,
DISTANCE
)
AS WITH COMPOUNDS1 (COMPONENT, ENCLOSING, DISTANCE) AS (
SELECT TASKID, MASTERID, 1
FROM BAT.TASKS
WHERE TASKID<>MASTERID
UNION ALL
SELECT A.TASKID MASTERID, B.ENCLOSING, B.DISTANCE+1
FROM BAT.TASKS A, COMPOUNDS1 B
WHERE A.MASTERID = B.COMPONENT
AND DISTANCE<10000 )
SELECT COMPONENT, ENCLOSING, DISTANCE
FROM COMPOUNDS1;

ALTER TABLE BAT.TASKS
ADD CONSTRAINT MASTERID FOREIGN KEY
(MASTERID
)
REFERENCES BAT.TASKS
(TASKID
)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

ALTER TABLE BAT.TASKS
ADD CONSTRAINT STARTID FOREIGN KEY
(TASKID,
STARTID
)
REFERENCES BAT.TASKLOG
(TASKID,
STARTID
)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

ALTER TABLE BAT.TASKS
ADD CONSTRAINT BACKWARDKIND CHECK
(BKWKIND=1);

ALTER TABLE BAT.TASKS
ADD CONSTRAINT ONWARDKIND CHECK
(ONWKIND=0);

ALTER TABLE BAT.TASKS
ADD CONSTRAINT RESTARTS CHECK
(RESTARTS in (0,1));

CREATE TABLE BAT.PROGPARMS
(PROGID CHARACTER(8) NOT NULL,
PARMNAME VARCHAR(18) NOT NULL,
DEFAULT VARCHAR(250) NOT NULL DEFAULT '',
MANDATORY DECIMAL(1, 0) NOT NULL DEFAULT 0,
REMARKS VARCHAR(250) NOT NULL DEFAULT '',
UPDATED TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
OPCODE SMALLINT NOT NULL DEFAULT 0
) IN BATCHSPACE;

ALTER TABLE BAT.PROGPARMS
ADD CONSTRAINT PROGPARMS_PK PRIMARY KEY
(PROGID,
PARMNAME
);
---
Having
42710 rows in BAT.TASKLOG,
33008 rows in BAT.TASKS,
706 rows in BAT.PROGPARMS,
after next command database is marked bad:
---
with allprogs(progid) as (select distinct t.progid from bat.compounds
c, bat.tasks t where c.component=t.taskid and c.enclosing=39592
union all select progid from bat.tasks where taskid=39592)
select p.parmname, p.progid, p.remarks
from allprogs a, bat.progparms p
where a.progid=p.progid and p.parmname not like '@%' order by p.parmname,
p.progid
---
This is extract from our production database, and we havn't seen such crash
before on db2 v6.1, v7.2, v8.1.0-2.
This crash appeared after applying FP3 or FP4.
I also tried db2 ese v8.1.3 with the same result.


Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.