Joined: 13 Jun 2012 Posts: 178 Location: United States
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.
Joined: 13 Jun 2012 Posts: 178 Location: United States
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.
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
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.
Joined: 13 Jun 2012 Posts: 178 Location: United States
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.
