Toad World® Forums

DDL Export of Table with LOB has bad syntax


#1

I extracted a table that has a BLOB field and the extracted DDL seems to have an
invalid statement in the create table statement.

Specifically

“DB2_GENERATED_ROWID_FOR_LOBS” ROWID NOT NULL IMPLICITLY HIDDEN GENERATED
ALWAYS,

With this statement included in the Create, the table cannot be created.

Thanks

Mark.

– Script created by Quest Extract DDL at 7/15/2010 10:42:53 AM for DB2PLOC
(Mark)

– System Objects may have been excluded from this script.

SET SCHEMA = ‘MARK’ ;

SET CURRENT SQLID = ‘MARK’ ;

CREATE STOGROUP “FSGALLP”

VOLUMES ( ‘*’ )

VCAT DB2P ;

CREATE DATABASE “DSNDB06”

INDEXBP BP0

CCSID EBCDIC ;

CREATE DATABASE “FDBMD”

BUFFERPOOL BP3

INDEXBP BP1

STOGROUP “FSGALLP”

CCSID EBCDIC ;

CREATE TABLESPACE “SYSEBCDC”

IN “DSNDB06”

USING VCAT “00000001”

FREEPAGE 0

PCTFREE 7

GBPCACHE CHANGED

TRACKMOD YES

COMPRESS NO

DEFINE YES

LOGGED

SEGSIZE 4

BUFFERPOOL “BP0”

CCSID EBCDIC

CLOSE NO

LOCKMAX SYSTEM

LOCKSIZE PAGE

MAXROWS 255 ;

CREATE TABLESPACE “FTSANNOT”

IN “FDBMD”

USING STOGROUP “FSGALLP”

PRIQTY 75000 SECQTY 25000 ERASE NO

FREEPAGE 0

PCTFREE 5

GBPCACHE CHANGED

TRACKMOD YES

COMPRESS NO

DEFINE YES

LOGGED

BUFFERPOOL “BP10”

CCSID EBCDIC

CLOSE NO

LOCKMAX SYSTEM

LOCKSIZE PAGE

MAXROWS 255 ;

CREATE LOB TABLESPACE “FTSANNOA”

IN “FDBMD”

USING STOGROUP “FSGALLP”

PRIQTY 25000 SECQTY 12504 ERASE NO

GBPCACHE CHANGED

DEFINE YES

LOGGED

DSSIZE 4 G

BUFFERPOOL “BP8K3”

CLOSE NO

LOCKMAX SYSTEM

LOCKSIZE ANY;

CREATE TABLE “SYSIBM”.“SYSDUMMY1” (

“IBMREQD” CHARACTER ( 1 ) FOR SBCS DATA NOT NULL

)

IN “DSNDB06”.“SYSEBCDC”

AUDIT NONE

DATA CAPTURE NONE

NOT VOLATILE

APPEND NO

CCSID EBCDIC ;

CREATE TABLE “FDBMD”.“ANNOTATIONS” (

“ANNOTATION_ID” INTEGER NOT NULL GENERATED BY DEFAULT

AS IDENTITY (START WITH 0 , INCREMENT BY 1 , CACHE 20 ,

MINVALUE - 2147483648 , NO MAXVALUE, CYCLE, NO ORDER ),

“CLAIM_ID” INTEGER NOT NULL ,

“FAMILY_ID” INTEGER NOT NULL ,

“RELATION_ID” SMALLINT NOT NULL ,

“PART_SSN” INTEGER NOT NULL ,

“PAT_SSN” INTEGER NOT NULL ,

“PART_FNAME” VARCHAR ( 40 ) FOR SBCS DATA WITH DEFAULT NULL ,

“PART_LNAME” VARCHAR ( 40 ) FOR SBCS DATA WITH DEFAULT NULL ,

“PAT_FNAME” VARCHAR ( 40 ) FOR SBCS DATA WITH DEFAULT NULL ,

“PAT_LNAME” VARCHAR ( 40 ) FOR SBCS DATA WITH DEFAULT NULL ,

“ANNOTATION” VARCHAR ( 3700 ) FOR SBCS DATA WITH DEFAULT NULL ,

“FLAG” BLOB (4K) WITH DEFAULT NULL ,

“CREATE_USERID” VARCHAR ( 40 ) FOR SBCS DATA WITH DEFAULT NULL ,

“CREATE_DATE” TIMESTAMP WITH DEFAULT NULL ,

“DB2_GENERATED_ROWID_FOR_LOBS” ROWID NOT NULL IMPLICITLY HIDDEN GENERATED
ALWAYS,

CONSTRAINT “ANNOTATION_ID” PRIMARY KEY

(“ANNOTATION_ID”)

)

IN “FDBMD”.“FTSANNOT”

AUDIT NONE

DATA CAPTURE NONE

NOT VOLATILE

APPEND NO

CCSID EBCDIC ;

CREATE AUX TABLE “FDBMD”.“ANNOTATIONSA”

IN “FDBMD”.“FTSANNOA”

STORES “FDBMD”.“ANNOTATIONS” COLUMN “FLAG” ;

CREATE UNIQUE INDEX “FDBMD”.“ANNOTATIONSA_X”

ON “FDBMD”.“ANNOTATIONSA”

USING STOGROUP “FSGALLP”

PRIQTY 100 SECQTY 52 ERASE NO

FREEPAGE 0

PCTFREE 10

GBPCACHE CHANGED

DEFINE YES

COMPRESS NO

PADDED

BUFFERPOOL BP11

CLOSE NO

PIECESIZE 4G

COPY YES ;

CREATE UNIQUE INDEX “FDBMD”.“XANNTAT1”

ON “FDBMD”.“ANNOTATIONS”

( “ANNOTATION_ID” ASC )

USING STOGROUP “FSGALLP”

PRIQTY 500 SECQTY 200 ERASE NO

FREEPAGE 0

PCTFREE 10

GBPCACHE CHANGED

DEFINE YES

COMPRESS NO

NOT CLUSTER

BUFFERPOOL BP11

CLOSE NO

PIECESIZE 2G

COPY YES ;

CREATE INDEX “FDBMD”.“XANNTAT2”

ON “FDBMD”.“ANNOTATIONS”

( “CLAIM_ID” ASC )

USING STOGROUP “FSGALLP”

PRIQTY 15000 SECQTY 3500 ERASE NO

FREEPAGE 0

PCTFREE 10

GBPCACHE CHANGED

DEFINE YES

COMPRESS NO

CLUSTER

BUFFERPOOL BP11

CLOSE NO

PIECESIZE 2G

COPY YES ;

CREATE INDEX “FDBMD”.“XANNTAT3”

ON “FDBMD”.“ANNOTATIONS”

( “FAMILY_ID” ASC , “RELATION_ID” ASC )

USING STOGROUP “FSGALLP”

PRIQTY 752 SECQTY 352 ERASE NO

FREEPAGE 0

PCTFREE 10

GBPCACHE CHANGED

DEFINE YES

COMPRESS NO

NOT CLUSTER

BUFFERPOOL BP11

CLOSE NO

PIECESIZE 2G

COPY YES ;

CREATE INDEX “FDBMD”.“XANNTAT4”

ON “FDBMD”.“ANNOTATIONS”

( “PART_SSN” ASC )

USING STOGROUP “FSGALLP”

PRIQTY 20000 SECQTY 5000 ERASE NO

FREEPAGE 0

PCTFREE 10

GBPCACHE CHANGED

DEFINE YES

COMPRESS NO

NOT CLUSTER

BUFFERPOOL BP11

CLOSE NO

PIECESIZE 2G

COPY YES ;

CREATE INDEX “FDBMD”.“XANNTAT5”

ON “FDBMD”.“ANNOTATIONS”

( “PAT_SSN” ASC )

USING STOGROUP “FSGALLP”

PRIQTY 30000 SECQTY 6000 ERASE NO

FREEPAGE 0

PCTFREE 10

GBPCACHE CHANGED

DEFINE YES

COMPRESS NO

NOT CLUSTER

BUFFERPOOL BP11

CLOSE NO

PIECESIZE 2G

COPY YES ;

BIND PACKAGE (FDBMD)

OWNER (MARK)

QUALIFIER (MARK)

MEMBER (RETRIEVE_COMPLETE_CLAIM)

ACTION ( ADD )

CURRENTDATA ( NO )

DBPROTOCOL (DRDA)

DEGREE ( 1 )

DYNAMICRULES (RUN)

ENCODING ( 37 )

EXPLAIN ( NO )

IMMEDWRITE ( NO )

ISOLATION (CS)

KEEPDYNAMIC ( NO )

REOPT (NONE)

RELEASE ( COMMIT )

ROUNDING (HALFEVEN)

SQLERROR (NOPACKAGE)

VALIDATE (RUN) ;

BIND PACKAGE (FDBMD)

OWNER (MARK)

QUALIFIER (MARK)

MEMBER (RETRIEVE_ANNOTATIONS_BY_CLAIMID)

ACTION ( ADD )

CURRENTDATA ( NO )

DBPROTOCOL (DRDA)

DEGREE ( 1 )

DYNAMICRULES (RUN)

ENCODING ( 37 )

EXPLAIN ( NO )

IMMEDWRITE ( NO )

ISOLATION (CS)

KEEPDYNAMIC ( NO )

REOPT (NONE)

RELEASE ( COMMIT )

ROUNDING (HALFEVEN)

SQLERROR (NOPACKAGE)

VALIDATE (RUN) ;

BIND PACKAGE (FDBMD)

OWNER (MARK)

QUALIFIER (MARK)

MEMBER (CREATE_ANNOTATION)

ACTION ( ADD )

CURRENTDATA ( NO )

DBPROTOCOL (DRDA)

DEGREE ( 1 )

DYNAMICRULES (RUN)

ENCODING ( 37 )

EXPLAIN ( NO )

IMMEDWRITE ( NO )

ISOLATION (CS)

KEEPDYNAMIC ( NO )

REOPT (NONE)

RELEASE ( COMMIT )

ROUNDING (HALFEVEN)

SQLERROR (NOPACKAGE)

VALIDATE (RUN) ;

BIND PACKAGE (FDBMD)

OWNER (MARK)

QUALIFIER (MARK)

MEMBER (MIGRATE_ANNOTATION)

ACTION ( ADD )

CURRENTDATA ( NO )

DBPROTOCOL (DRDA)

DEGREE ( 1 )

DYNAMICRULES (RUN)

ENCODING ( 37 )

EXPLAIN ( NO )

IMMEDWRITE ( NO )

ISOLATION (CS)

KEEPDYNAMIC ( NO )

REOPT (NONE)

RELEASE ( COMMIT )

ROUNDING (HALFEVEN)

SQLERROR (NOPACKAGE)

VALIDATE (RUN) ;

BIND PACKAGE (FDBMD)

OWNER (MARK)

QUALIFIER (MARK)

MEMBER (MIGRATE_ANNOTATION_NULLFLAG)

ACTION ( ADD )

CURRENTDATA ( NO )

DBPROTOCOL (DRDA)

DEGREE ( 1 )

DYNAMICRULES (RUN)

ENCODING ( 37 )

EXPLAIN ( NO )

IMMEDWRITE ( NO )

ISOLATION (CS)

KEEPDYNAMIC ( NO )

REOPT (NONE)

RELEASE ( COMMIT )

ROUNDING (HALFEVEN)

SQLERROR (NOPACKAGE)

VALIDATE (RUN) ;

BIND PACKAGE (FODBCOLL)

OWNER (MARK)

QUALIFIER (MARK)

MEMBER (SQL76980)

LIBRARY ( ‘DB2P.DBRMLIB.C’ )

DEFER ( PREPARE )

ACTION ( ADD )

CURRENTDATA ( NO )

DBPROTOCOL (DRDA)

DEGREE ( ANY )

ENCODING ( 37 )

EXPLAIN (YES)

IMMEDWRITE ( NO )

ISOLATION (CS)

KEEPDYNAMIC ( NO )

REOPT (NONE)

RELEASE ( COMMIT )

ROUNDING (HALFEVEN)

SQLERROR (NOPACKAGE)

VALIDATE ( BIND ) ;

BIND PACKAGE (FODBMDFY)

OWNER (MARK)

QUALIFIER (MARK)

MEMBER (SQL26293)

LIBRARY ( ‘DB2P.DBRMLIB.C’ )

ACTION ( ADD )

CURRENTDATA (YES)

DBPROTOCOL (DRDA)

DEGREE ( 1 )

ENCODING ( 37 )

EXPLAIN (YES)

IMMEDWRITE ( NO )

KEEPDYNAMIC ( NO )

REOPT (NONE)

RELEASE ( COMMIT )

ROUNDING (HALFEVEN)

SQLERROR (NOPACKAGE)

VALIDATE (RUN) ;

BIND PACKAGE (FODBMDFY)

OWNER (MARK)

QUALIFIER (MARK)

MEMBER (SQL68009)

LIBRARY ( ‘DB2P.DBRMLIB.C’ )

DEFER ( PREPARE )

ACTION ( ADD )

CURRENTDATA (YES)

DBPROTOCOL (DRDA)

DEGREE ( ANY )

ENCODING ( 37 )

EXPLAIN (YES)

IMMEDWRITE (YES)

ISOLATION (CS)

KEEPDYNAMIC ( NO )

REOPT (NONE)

RELEASE ( COMMIT )

ROUNDING (HALFEVEN)

SQLERROR (NOPACKAGE)

VALIDATE ( BIND ) ;

BIND PACKAGE (FODBMDFY)

OWNER (MARK)

QUALIFIER (MARK)

MEMBER (SQL90124)

LIBRARY ( ‘DB2P.DBRMLIB.C’ )

DEFER ( PREPARE )

ACTION ( ADD )

CURRENTDATA (YES)

DBPROTOCOL (DRDA)

DEGREE ( ANY )

ENCODING ( 37 )

EXPLAIN (YES)

IMMEDWRITE (YES)

ISOLATION (CS)

KEEPDYNAMIC ( NO )

REOPT (NONE)

RELEASE ( COMMIT )

ROUNDING (HALFEVEN)

SQLERROR (NOPACKAGE)

VALIDATE ( BIND ) ;


#2

Mark,

Thank you for the input. I’ve opened cr 76,039.

Nancy Alsip