Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Incomplete table definition?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
John Poulakos

New User


Joined: 13 Jun 2012
Posts: 49
Location: United States

PostPosted: Tue Aug 07, 2012 9:48 pm    Post subject: Incomplete table definition?
Reply with quote

Well, I almost made it! I got 2 out of 3 tables defined and loaded correctly. But, my luck ran out on the last one. I'm getting an INCOMPLETE TABLE DEFINITION error on the LOAD attempt. I guessed (obviously incorrectly) that this was due to having no index in the definitions I was provided, so I created one. That didn't fix the problem. As far as I can tell, the definition I was given and the tables parameters created in SYSPUNCH by the UNLOAD utility and used as input to the LOAD, are identical. My next best guess is the indexes it was unloaded with don't match the one I created from scratch, but my guesses haven't been real good so far.

The next time I'm given a test database to recreate I'm hiding somewhere.
Back to top
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Tue Aug 07, 2012 10:10 pm    Post subject:
Reply with quote

Is at least one index unique, and is a unique index defined as the primary index?
Back to top
View user's profile Send private message
John Poulakos

New User


Joined: 13 Jun 2012
Posts: 49
Location: United States

PostPosted: Tue Aug 07, 2012 10:14 pm    Post subject: Reply to: Incomplete table definition?
Reply with quote

I created one index from scratch, the original table had 5 indexes that I could see from some explain data. But, I don't know the contents of those indexes.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Tue Aug 07, 2012 10:27 pm    Post subject:
Reply with quote

The message for a -530 SQLCODE is "THE DEFINITION OF TABLE table-name IS INCOMPLETE BECAUSE IT LACKS A PRIMARY INDEX OR A REQUIRED UNIQUE INDEX". Check the possible reasons and take appropriate action.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Aug 07, 2012 10:43 pm    Post subject:
Reply with quote

you have on the existing system,
all these tables.

here is some sql i use to determine existing indexes.

Code:

  SELECT SUBSTR(I.TBNAME,1,20) AS TBNAME                               
       , SUBSTR(K.IXNAME,1,12) AS IXNAME                               
       , SUBSTR(K.COLNAME,1,16) AS COLNAME                             
       , K.COLSEQ AS SEQ                                               
       , REPLACE(REPLACE(SUBSTR(DIGITS(K.COLNO),2,4),'00','  ')         
           ,' 0', '  ') AS COLN                                         
       , I.UNIQUERULE AS U_D                                           
       , K.ORDERING AS ORD                                             
       , SUBSTR(COLTYPE,1,8) AS COLTYPE                                 
       , LENGTH AS LEN                                                 
       , NULLS                                                         
  FROM SYSIBM.SYSKEYS K                                                 
     , SYSIBM.SYSINDEXES I                                             
     , SYSIBM.SYSCOLUMNS C                                             
  WHERE K.IXCREATOR = 'your index creator name'                                         
    AND I.TBCREATOR = 'your table creator name'                                         
    AND C.TBCREATOR = 'your table creator name'                                         
    AND I.NAME = K.IXNAME                                               
    AND C.NAME = K.COLNAME                                             
    AND I.TBNAME = C.TBNAME                                             
    AND I.TBNAME NOT IN (                                               
                        'DSN_FUNCTION_TABLE'                           
                       ,'DSN_STATEMENT_CACHE_AUX'                       
                       ,'DSN_STATEMENT_CACHE_TABLE'                     
                       ,'DSN_STATEMNT_TABLE'                           
                       ,'PLAN_HISTORY'                                 
                       ,'PLAN_TABLE'                                   
                      )                                                 
  ORDER BY 1, 2, 4                                                     
  WITH UR                                                               
  ;               

in the case you do not know the creator names,
remove the where clauses,
look at the output, and then use the creator names that you want
and rerun the sql.
Back to top
View user's profile Send private message
John Poulakos

New User


Joined: 13 Jun 2012
Posts: 49
Location: United States

PostPosted: Wed Aug 08, 2012 12:05 am    Post subject: Reply to: Incomplete table definition?
Reply with quote

To recap, I am attempting to build a test database on a small mainframe that has very little DB2 stuff on it. The database is being built from UNLOAD utility output (SYSPUNCH and SYSREC data for 3 tables) from a computer that's not available to us. The only other data I have is the database definitions for the 3 tables, tablespaces, indexes, etc. I am lacking the indexes for the third table. My only other resource is a person who is familiar with this database, but he is very hard to contact and is 7 hours ahead of me.

So far, it's been been real fun... like a root canal! "You just need to define the tables with SPUFI and run the LOAD utility with the SYSPUNCH data"!

Anyway, I managed to construct an acceptable index, thanks to the help you folks have provided me. I fired up my LOAD utility, it ran for 5 minutes and I ran out of space on the disk volumes. I need a beer... or 6!

Thanks once again to everyone. I'd still be sitting in a tree, flinging poo at passerbys, figuratively, without your help.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Aug 08, 2012 12:24 am    Post subject: Reply to: Incomplete table definition?
Reply with quote

Isn't progress wonderful - and it is not even Friday icon_smile.gif
Maybe you can get in some practice beer before the weekend. . .

Thanks for the update!

d
Back to top
View user's profile Send private message
Pedro

Senior Member


Joined: 01 Sep 2006
Posts: 2019
Location: Silicon Valley

PostPosted: Wed Aug 08, 2012 2:19 am    Post subject: Reply to: Incomplete table definition?
Reply with quote

Quote:
I'd still be sitting in a tree, flinging poo at passerbys...


Most of my career was like that.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm
No new posts Getting soc4 on internal table in cobol abdulrafi COBOL Programming 13 Fri May 06, 2016 3:39 pm
No new posts Difference space showed for TS and it... autobox DB2 1 Thu Apr 14, 2016 1:07 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us