Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL1476N   CREATE TABLE NOT LOGGED INITIALLY FAILS...

Posted on 2003-12-04
6
Medium Priority
?
2,994 Views
Last Modified: 2007-12-19
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_TYPCD,TRADE_CODE,postcode)
)
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_TYPCD,TRADE_CODE,postcode)
)
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 =>
0
Comment
Question by:Lowfatspread
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 50

Author Comment

by:Lowfatspread
ID: 9878386
sorry DB2 UDB 7.2 on UNIX

0
 
LVL 18

Accepted Solution

by:
BigSchmuh earned 1000 total points
ID: 9888240
Yes, you can NOT define a primary (neither a unique) key on Nullable columns.
==> The expression "Col1 = NULL" is evaluated to NULL whatever Col1 is and no dbms is expected to work with Null values outside the scope of "Is Null" or "Is Not Null" operators (Which means that some dbms does this...MS-Jet or Oracle used to allow creation of Unique index on nullable columns but I think none will allow that on Primary key)

Anyway, why don't you create your Primary key on a SEQUENCE based column ?

Hope this helps.
0
 
LVL 50

Author Comment

by:Lowfatspread
ID: 9888525
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...    
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 18

Expert Comment

by:BigSchmuh
ID: 9891497
Creating new primary keys based on sequence may save huge time if you need to make this 4 col joins more than once.
0
 

Expert Comment

by:Zhousanbao
ID: 10014230
   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.
   
0
 
LVL 50

Author Comment

by:Lowfatspread
ID: 10015341
v8 in my dreams ...

that 'll be 2006 at the earliest
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question