Lowfatspread
asked on
SQL1476N CREATE TABLE NOT LOGGED INITIALLY FAILS...
iS THE REASON THIS FAILS ANYTHING TO DO with the fact my primary key
ends with nullable columns....
the second create table works if just the tradegroup column is allowed to be nullable...
db2 => CREATE TABLE stg.tmp_aGR_TRADE
(
AGREEMENT_ID INTEGER NOT NULL,
AGREEMENT_TYPCD INTEGER NOT NULL,
TRADE_CODE CHAR(4) ,
TRADEgROUP CHAR(4) ,
poSTCODE CHAR(4) ,
risks integer not null,
primary key (AGREEMENT_id,AGREEMENT_TY PCD,TRADE_ CODE,postc ode)
)
IN heapSPACE
INDEX IN heapSPACE
NOT LOGGED INITIALLY;db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) =>
db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2
(cont.) => db2 (cont.) => db2 (cont.) =>
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1476N The current transaction was rolled back because of error "-542".
SQLSTATE=40506
db2 => CREATE TABLE stg.tmp_aGR_TRADE
(
AGREEMENT_ID INTEGER NOT NULL,
AGREEMENT_TYPCD INTEGER NOT NULL,
TRADE_CODE CHAR(4) NOT NULL,
TRADEgROUP CHAR(4) ,
poSTCODE CHAR(4) NOT NULL,
risks integer not null,
primary key (AGREEMENT_id,AGREEMENT_TY PCD,TRADE_ CODE,postc ode)
)
IN heapSPACE
INDEX IN heapSPACE
NOT LOGGED INITIALLY;
db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (
cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.
) => db2 (cont.) => DB20000I The SQL command completed successfully.
db2 =>
ends with nullable columns....
the second create table works if just the tradegroup column is allowed to be nullable...
db2 => CREATE TABLE stg.tmp_aGR_TRADE
(
AGREEMENT_ID INTEGER NOT NULL,
AGREEMENT_TYPCD INTEGER NOT NULL,
TRADE_CODE CHAR(4) ,
TRADEgROUP CHAR(4) ,
poSTCODE CHAR(4) ,
risks integer not null,
primary key (AGREEMENT_id,AGREEMENT_TY
)
IN heapSPACE
INDEX IN heapSPACE
NOT LOGGED INITIALLY;db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) =>
db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2
(cont.) => db2 (cont.) => db2 (cont.) =>
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1476N The current transaction was rolled back because of error "-542".
SQLSTATE=40506
db2 => CREATE TABLE stg.tmp_aGR_TRADE
(
AGREEMENT_ID INTEGER NOT NULL,
AGREEMENT_TYPCD INTEGER NOT NULL,
TRADE_CODE CHAR(4) NOT NULL,
TRADEgROUP CHAR(4) ,
poSTCODE CHAR(4) NOT NULL,
risks integer not null,
primary key (AGREEMENT_id,AGREEMENT_TY
)
IN heapSPACE
INDEX IN heapSPACE
NOT LOGGED INITIALLY;
db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (
cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.
) => db2 (cont.) => DB20000I The SQL command completed successfully.
db2 =>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes thanks schmuh, it had been a late and frustrating night at that stage....
on os390 you can have unique where not null indexes...
my primary key is basically my business key...
this is a temporary table to enable me to gather some min/max information
prior to populating the final wharehouse tables during my ETL process...
its a 4 way join at this stage and i'm trying to minimise index requirements...
on os390 you can have unique where not null indexes...
my primary key is basically my business key...
this is a temporary table to enable me to gather some min/max information
prior to populating the final wharehouse tables during my ETL process...
its a 4 way join at this stage and i'm trying to minimise index requirements...
Creating new primary keys based on sequence may save huge time if you need to make this 4 col joins more than once.
Take advantange of performence, I do not advice to use sequence .Can we change (trade_code char(4) not null ,poSTCODE CHAR(4) NOT NULL) to (trade_code smallint not null ,poSTCODE smallint NOT NULL)?
I think you can get good performence especially this table is a fact table.
By the way, If you upgrade to db2 udb v8 ,you can use mdc table to improve the performence.
I think you can get good performence especially this table is a fact table.
By the way, If you upgrade to db2 udb v8 ,you can use mdc table to improve the performence.
ASKER
v8 in my dreams ...
that 'll be 2006 at the earliest
that 'll be 2006 at the earliest
ASKER