Here is a simple script to reproduce the aforementioned error. DBEXTRACT
sometimes will succeed, other times not. Once it does fail, all subsequent
attemts fail. TABLEEXTRACT does not work either.
Schema:
----------------------------------------------------------------------------
USE USER DBA DBA
//
AUTOCOMMIT OFF
//
CREATE USERGROUP ADMIN RESOURCE NOT EXCLUSIVE
//
CREATE USER owens PASSWORD owens USERGROUP ADMIN
//
COMMIT
//
USE USER owens owens
//
CREATE TABLE person
(
id INTEGER NOT NULL,
firstname VARCHAR(15) NOT NULL,
lastname VARCHAR(20) NOT NULL,
ex_time TIMESTAMP DEFAULT '20010101000000000000',
ex_date Date DEFAULT '20010101',
PRIMARY KEY (id)
)
//
CREATE INDEX t_ln_idx ON person(lastname ASC)
//
create sequence cno_s
//
insert into person (id, firstname, lastname) values (cno_s.NEXTVAL,
'John','Doe')
//
insert into person (id, firstname, lastname) values (cno_s.nextval,
'Jane','Doe')
//
COMMIT
----------------------------------------------------------------------------
Export Script:
----------------------------------------------------------------------------
USE USER DBA DBA
//
catalogextract ALL OUTSTREAM FILE '/tmp/sap/catalog.txt'
//
dbextract CATALOG OUTSTREAM FILE '/tmp/sap/cat'
DATA OUTSTREAM FILE '/tmp/sap/dat'
//
tableextract ALL DATA OUTSTREAM '/tmp/sap/tbl.all'
----------------------------------------------------------------------------
This results in:
----------------------------------------------------------------------------
Opened connection to Loader SERVER at node local host.
REPLICATION SERVER Log File: '/usr/data/sapdb/indep_data/wrk/loader.prt'
User DBA connected to database IRIS on local host.
USE USER DBA DBA
Successfully executed
catalogextract ALL OUTSTREAM FILE '/tmp/sap/catalog.txt'
Successfully executed
dbextract CATALOG OUTSTREAM FILE '/tmp/sap/cat' DATA OUTSTREAM FILE
'/tmp/sap/dat'
Replication server error (-4 : 'protocol error')
Some error(s) occurred running command.
---> See log file(s) for more information.
----------------------------------------------------------------------------
loader.prt:
----------------------------------------------------------------------------
dbextract CATALOG OUTSTREAM FILE '/tmp/sap/cat' DATA OUTSTREAM FILE
'/tmp/sap/dat'
// *
// M LOAD TRANSFORMATIONPACKAGE
x'02000000D27EF33D3F0E0000B4E8901F0AC80A0A00000000'
// *
// M Number of tables to transform: 1
// *
// M Number of views to transform: 0
// *
// M Number of synonyms to transform: 0
// *
UPDATE TRANSFORMATIONMODEL SET PACKAGEGUID =
x'02000000D27EF33D3F0E0000B4E8901F0AC80A0A00000000' WHERE TASKID = 0
// *
// E -25392: '[SAP AG][LIBSQLOD SO][SAP DB]Integrity constraint
violation;200 POS(1) Duplicate key.'
// E -25392: '200'
// M EXECUTE PACKAGE TO TRANSFORM CATALOG
// *
// M Number of tables transformed : 2
// *
// M Number of views transformed : 0
// *
// M Number of synonyms transformed : 0
// *
// M CONFIGURE TRANSFORMATIONPACKAGE
// *
UPDATE TRANSFORMATIONMODEL SET SERVERNODE = 'mike', SERVERDB = 'IRIS',
CODETYPE = 'ASCII', USERNAME = 'DBA', CODEPAGEID = '8859-1', STORAGEDEVICE
= 'NONE', TRANSFORMATION = 'EXTRACT', PART = 'ALL', DATASTREAMTYPE = 'FILE',
DATASTREAMFORMAT = , TRANSACTIONSIZE = 0, EXCLUDE = FALSE , RESTART =
TRUE , STARTTIME = NULL , ENDTIME = NULL WHERE PACKAGEGUID =
x'02000000D27EF33D3F0E0000B4E8901F0AC80A0A00000000'
// *
// E -25392: ''
// M EXECUTE PACKAGE TO TRANSFORM DATA
// *
----------------------------------------------------------------------------
knldiag:
----------------------------------------------------------------------------
2002-12-08 11:18:01 3535 11007 COMMUNIC wait for connection T29
2002-12-08 11:18:09 3505 11561 COMMUNIC Connecting T28 mike.c21bowman.com
3647
2002-12-08 11:18:09 3535 11561 COMMUNIC Connected T28 mike.c21bowman.com
3647
2002-12-08 11:18:09 3534 11560 COMMUNIC Releasing T28
2002-12-08 11:18:09 3534 11007 COMMUNIC wait for connection T28
2002-12-08 11:18:09 3505 11561 COMMUNIC Connecting T28 mike.c21bowman.com
3647
2002-12-08 11:18:09 3534 11561 COMMUNIC Connected T28 mike.c21bowman.com
3647
2002-12-08 11:18:09 3505 11561 COMMUNIC Connecting T29 mike.c21bowman.com
3647
2002-12-08 11:18:09 3534 11561 COMMUNIC Connected T29 mike.c21bowman.com
3647
2002-12-08 11:18:10 3535 11560 COMMUNIC Releasing T29
2002-12-08 11:18:10 3535 11007 COMMUNIC wait for connection T29
2002-12-08 11:18:10 3505 11561 COMMUNIC Connecting T29 mike.c21bowman.com
3647
2002-12-08 11:18:10 3534 11561 COMMUNIC Connected T29 mike.c21bowman.com
3647
2002-12-08 11:18:10 3535 11560 COMMUNIC Releasing T29
2002-12-08 11:18:10 3535 11007 COMMUNIC wait for connection T29
2002-12-08 11:18:10 3505 11561 COMMUNIC Connecting T29 mike.c21bowman.com
3647
2002-12-08 11:18:10 3534 11561 COMMUNIC Connected T29 mike.c21bowman.com
3647
2002-12-08 11:18:11 3504 WNG 11840 COMMUNIC Killing T28 for died apid 3647
2002-12-08 11:18:11 3504 WNG 11840 COMMUNIC Killing T29 for died apid 3647
2002-12-08 11:18:11 3534 WNG 11804 COMMUNIC application dead for T28
2002-12-08 11:18:11 3534 WNG 11824 COMMUNIC Releasing T28 connection aborted
2002-12-08 11:18:11 3534 11007 COMMUNIC wait for connection T28
2002-12-08 11:18:11 3535 WNG 11804 COMMUNIC application dead for T29
2002-12-08 11:18:11 3535 WNG 11824 COMMUNIC Releasing T29 connection aborted
2002-12-08 11:18:11 3535 11007 COMMUNIC wait for connection T29
--------------------------------------- current write position --------------
Finally, the contents of CATALOGEXTRACT (catalog.txt) are:
----------------------------------------------------------------------------
SQLMODE INTERNAL
//
USE USER DBA DBA
//
CREATE USER TEST PASSWORD initial DBA NOT EXCLUSIVE
//
CREATE USERGROUP ADMIN RESOURCE NOT EXCLUSIVE
//
CREATE USER OWENS PASSWORD initial USERGROUP ADMIN
//
USE USER ADMIN initial
//
CREATE TABLE "ADMIN"."PERSON"
(
"ID" Integer,
"FIRSTNAME" Varchar (15) ASCII NOT NULL,
"LASTNAME" Varchar (20) ASCII NOT NULL,
"EX_TIME" Timestamp DEFAULT 20010101000000000000,
"EX_DATE" Date DEFAULT 20010101,
PRIMARY KEY ("ID")
)
//
CREATE INDEX "T_LN_IDX" ON "ADMIN"."PERSON" ("LASTNAME" ASC )
//
CREATE TABLE "ADMIN"."PERSON"
(
"ID" Integer,
"FIRSTNAME" Varchar (15) ASCII NOT NULL,
"LASTNAME" Varchar (20) ASCII NOT NULL,
"EX_TIME" Timestamp DEFAULT 20010101000000000000,
"EX_DATE" Date DEFAULT 20010101,
PRIMARY KEY ("ID")
)
//
CREATE INDEX "T_LN_IDX" ON "ADMIN"."PERSON" ("LASTNAME" ASC )
//
CREATE SEQUENCE "ADMIN"."CNO_S" INCREMENT BY 1 START WITH 2 MAXVALUE
99999999999999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 ORDER
//
//
USE USER DBA DBA
----------------------------------------------------------------------------
which indicates that CATALOGEXTRACT is not delimiting the default values of
date and timestamps with single quotes. Running the output of CATALOGEXTRACT
on a new, empty database yields:
----------------------------------------------------------------------------
Opened connection to Loader SERVER at node local host.
REPLICATION SERVER Log File: '/usr/data/sapdb/indep_data/wrk/loader.prt'
User DBA connected to database IRIS on local host.
SQLMODE INTERNAL
Successfully executed
USE USER DBA DBA
Successfully executed
CREATE USERGROUP ADMIN RESOURCE NOT EXCLUSIVE
Successfully executed
CREATE USER OWENS PASSWORD owens USERGROUP ADMIN
Successfully executed
USE USER owens owens
Successfully executed
CREATE TABLE "ADMIN"."PERSON"
(
"ID" Integer,
"FIRSTNAME" Varchar (15) ASCII NOT NULL,
"LASTNAME" Varchar (20) ASCII NOT NULL,
"EX_TIME" Timestamp DEFAULT 20010101000000000000,
"EX_DATE" Date DEFAULT 20010101,
PRIMARY KEY ("ID")
)
Error during execution
-->-25010
SQL error -8004 = Constant must be compatible with column type and length
(error position: 225)
Defined maximum number of errors (1) reached
1 error(s) during execution of command file 'catalog.txt'.
---> See log file(s) for more information.
Some error(s) occurred running command.
---> See log file(s) for more information.
----------------------------------------------------------------------------
I am not the most adept user of SAP DB. I am just now returning to it after a
few months time to test/learn the 7.4 beta. I have tried to follow the
documentation as closely as I can, but still make allowance for the
possibility that I have missed something(s). An important aspect of SAP DB,
for me, is the ability to make consistent dumps of the database, catalog, and
tables, thus the reason I am working with the new loadercli.
I think SAP DB is incredible. I want to standardize on it for our in-house
software. I am just trying to ensure that we can get the data in and out of
the database in a relatively portable format, which IMHO is through both
TABLEEXTRACT and DBEXTRACT.
Michael Owens
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
