| View previous topic :: View next topic |
| Author |
Message |
kingo
Intermediate
Joined: 01 Sep 2006
Posts: 167
Topics: 40
Location: chennai
|
 Posted: Wed Jul 18, 2007 10:44 pm Post subject: unload data |
 |
|
Is it possible to unload data all the tables from the database into different files.
Regds,
Kingo
_________________
IF YOU ARE NOT FOCUSSED ON GOAL ALL YOU SEE IS OBSTACLE. |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
kingo
Intermediate
Joined: 01 Sep 2006
Posts: 167
Topics: 40
Location: chennai
|
| Posted: Thu Jul 19, 2007 7:40 am Post subject: |
|
|
No my question is that I have some hundreds of tables in my database I need to solve unload all of them into seperately is a not possible.so is it possible by any means so that all the tables in this database being unloaded.I have 30 such database.I need to do for evrything.Hope you got my problem.And I want my data of each table to be seperate and in readable form.
_________________
IF YOU ARE NOT FOCUSSED ON GOAL ALL YOU SEE IS OBSTACLE. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Jul 19, 2007 8:08 am Post subject: |
|
|
kingo,
Each unload job can unload upto 125 tables at a time and each table is unloaded to a different Dataset. So depending on how many tables you have divide the total no: of tables by 125 to come up with no: of unload jobs.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
kingo
Intermediate
Joined: 01 Sep 2006
Posts: 167
Topics: 40
Location: chennai
|
| Posted: Thu Jul 19, 2007 8:38 am Post subject: |
|
|
Yeah I know that.Its quite tedious to create cntl cards for all the tables.For ex I have a database with 140 tables.Its not that easy to create the control cards for all tables from that database.Is there are any alternative method which will build my unload job.
_________________
IF YOU ARE NOT FOCUSSED ON GOAL ALL YOU SEE IS OBSTACLE. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Jul 19, 2007 8:54 am Post subject: |
|
|
| Quote: |
Its quite tedious to create cntl cards for all the tables.For ex I have a database with 140 tables.Its not that easy to create the control cards for all tables from that database
|
kingo,
create the control cards using this sql
| Code: |
SELECT CHAR('SELECT * FROM ')
,CHAR(CREATOR)
,CHAR(SUBSTR(NAME,1,8))
,CHAR(';')
,CHAR(' ')
FROM SYSIBM.SYSTABLES
WHERE DBNAME = 'your dbname'
AND TYPE = 'T'
FETCH FIRST 125 ROWS ONLY
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Thu Jul 19, 2007 10:21 am Post subject: |
|
|
I used koluso's query and mixed it up a little(with things I learned from this board). The results are strung in one column.
| Code: |
SELECT CHAR('SELECT * FROM ') || RTRIM(CREATOR)|| ' ' || RTRIM(NAME)|| ' ;'
FROM SYSIBM.SYSTABLES
WHERE DBNAME = 'YOUR DBNAME'
AND TYPE = 'T'
FETCH FIRST 125 ROWS ONLY
|
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
kingo
Intermediate
Joined: 01 Sep 2006
Posts: 167
Topics: 40
Location: chennai
|
| Posted: Thu Jul 19, 2007 9:35 pm Post subject: |
|
|
Hi kolusu,
Thanks for u grt help.I have done a small modification which is now ready to use.
here it is
| Code: |
SELECT CHAR('SELECT * FROM ')
,CHAR(CREATOR)
,CHAR('.')
,CHAR(SUBSTR(NAME,1,8))
,CHAR(';')
,CHAR(' ')
FROM SYSIBM.SYSTABLES
WHERE DBNAME = 'DB name'
AND TYPE = 'T'
FETCH FIRST 125 ROWS ONLY
;
|
Regds,
Kingo
_________________
IF YOU ARE NOT FOCUSSED ON GOAL ALL YOU SEE IS OBSTACLE. |
|
| Back to top |
|
|
videlord
Beginner
Joined: 09 Dec 2004
Posts: 147
Topics: 19
|
| Posted: Thu Jul 19, 2007 11:23 pm Post subject: |
|
|
why not use UNLOAD and LIST, TEMPLATE utility
it's easy to unload all tables in one database |
|
| Back to top |
|
|
kingo
Intermediate
Joined: 01 Sep 2006
Posts: 167
Topics: 40
Location: chennai
|
| Posted: Fri Jul 20, 2007 12:21 am Post subject: |
|
|
How to do that can you help me please?
Regds,
Kingo
_________________
IF YOU ARE NOT FOCUSSED ON GOAL ALL YOU SEE IS OBSTACLE. |
|
| Back to top |
|
|
videlord
Beginner
Joined: 09 Dec 2004
Posts: 147
Topics: 19
|
| Posted: Fri Jul 20, 2007 7:17 pm Post subject: |
|
|
check the Utility Guide & Reference
Sample:
LISTDEF LS1 INCLUDE DATABASE db1
TEMPLATE UNLDREC DSN xxxx
UNIT SYSDA DISP (NEW,CATLG,CATLG) SPACE (x,x) CYL
TEMPLATE PUNCH1 DSN xxx
UNIT SYSDA DISP (NEW,CATLG,CATLG) SPACE(1,1) TRK
UNLOAD LIST LS1
PUNCHDDN PUNCH1
UNLDDN UNLDREC |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
