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
 

 

how we unload a db2 table into a file

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

New User


Joined: 25 May 2006
Posts: 2

PostPosted: Thu May 31, 2007 6:14 pm    Post subject: how we unload a db2 table into a file
Reply with quote

I have a db2 table named " customer-info " which contains information of a customers of diff states and cities of states who had taken hutch connection with diff plans with coloumn "status-code" 1 if new customer and 0 for old customer with bit format..primary key is plan-no ..i have to unload this
table in to a file using jcl of new customer having 1 status-code ... wht wll be jcl ?
if i have two file one "customer-details have information all customer detials having diff " plan-no " with diff states and cities and 2nd " payment-details" file having current month paid-payment detailsi have to make a cobol program which generate a report of this format...

i have to generate report file using two input file which is unloaded from db2 table not db2 access

Report ID: Summary Premium distribution by state and city
Page : 1
Date :
Time:
<< Month >> << Year >>
State u.p
Distribution in agra city
Total No of customer Paid payment =
Total paymypayment payment of customer in agra=

Distribution in lucknow
Total No of customer Paid payment =
Total paymypayment payment of customer in lucknow=

State panjab

Distribution in amritsar city
Total No of customer Paid payment =
Total paymypayment payment of customer in amritsar=


and so on ...for every state it will be a new page .......

how it wll be ........

thanks a regards
Back to top
View user's profile Send private message

murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Thu May 31, 2007 6:17 pm    Post subject:
Reply with quote

Vikas,

You may be new to mainframes. It doesn't mean, you have to post the same query more than once with different subjects under different forums. Please read the forum rules before you do post your queries.

This is the second post under DB2 forum and one more post under another forum.

Moderators,

Some action Please.
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: Thu May 31, 2007 8:25 pm    Post subject:
Reply with quote

Hello vikas,

When posting a question, it is a better idea to post what you have tried and what problem(s) you are having rather than posting your initial requirement definition.

If you are stuck on where to even begin, i'd suggest asking someone in your group as they should be able to provide general guidance on how such requirements are done at your location.

It is always advisable to follow the "standard" method at an organization rather than invent something new each time there is a requirement of this type.

When you post back with additional info, someone here will be able to make suggestions.


Murali - i was not able to find the "other" db2 topic. if you tell me where they are (the other db2 and the other forum posts), i will remove them.
Back to top
View user's profile Send private message
Abirami.YN

New User


Joined: 16 Jul 2005
Posts: 15
Location: India - Chennai

PostPosted: Fri Jun 01, 2007 11:19 pm    Post subject:
Reply with quote

Hi Vikas

1. You Can use below utility to unload table

//STEP1 EXEC PGM=IKJEFT1B,DYNAMNBR=20,TIME=120,REGION=3072K
//STEPLIB DD DSN=DBPT.DB2.SDSNLOAD,DISP=SHR
//ISPPROF DD UNIT=SYSDA,SPACE=(TRK,(9,1,4)),
// DCB=(LRECL=175,BLKSIZE=27825,RECFM=FB,DSORG=PO)
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//REPORT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DBP8)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB71) PARMS('SQL') -
LIB('DBP8.DB2.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSPUNCH DD DUMMY
//SYSREC00 DD DSN=AIS.D5750CPR.EDCSFILE,
// DISP=(NEW,CATLG,KEEP),
// SPACE=(CYL,(200,200),RLSE),
// UNIT=SYSDA,STORCLAS=SCNORMAL
//SYSIN DD *
SELECT * FROM tablename WHERE Condition;
/*

******************************************
SYSREC - is the Unload file
SYSPUNCH - If u need layout for unload file, then specify the dataset name.

2. You can use the fileaid option to unload table.

Regards,
Abirami.
Back to top
View user's profile Send private message
vikasmainframe

New User


Joined: 25 May 2006
Posts: 2

PostPosted: Sun Jun 03, 2007 12:03 pm    Post subject: sorry for ths is this is breaking any rule ...........
Reply with quote

i want to appologize for this ......plz delete my querry from forum .....i didnt mean tht .....i m new to mainframes ....tht's was my practial homework due to which i wanna learn more bout cobol and db2 ....i was stuck in logic tht's why i put my querry ......

sorry again .....

vikas
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: Sun Jun 03, 2007 1:54 pm    Post subject:
Reply with quote

Hello Vikas,

Please check for a reply PM (Private Message).
Back to top
View user's profile Send private message
girias

New User


Joined: 09 Feb 2007
Posts: 26
Location: Chennai

PostPosted: Thu Jun 14, 2007 11:39 am    Post subject:
Reply with quote

Hi,
I tried using this utility to unload from 10 tables. (sysrec00, sysrec01,..)

In one table which has more than 30 columns, the values are getting truncated as it has BLOB(250K) data type in column33

The error mesage is,
Code:

WARNING: DATA FROM COLUMN   33 WAS TRUNCATED TO    32755 BYTES FROM   256000.
WARNING: DATA FROM COLUMN   33 WAS TRUNCATED TO    27004 BYTES FROM    32755.


Is it possible to unload all the columns from the table omiting this column alone.

I tried this query. But I am getting error saying Invalid Keyword Except

Code:

SELECT * EXCEPT (col_33)
FROM   <table _name>
WHERE <condition>



or pls tell me how can I specify the rec-length for dataset to unload including this column also?

Thanks in advance for your quick response.

-Giri
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: Thu Jun 14, 2007 6:00 pm    Post subject:
Reply with quote

Hello,

Name only the columns you want and do not include the problem column(s).

Over time, you will find that 30 columns is not actually very many. . . .
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 FTP xls file from Mainfrmae kumarsatheeshc1 TSO/ISPF 5 Tue Jun 20, 2017 6:56 pm
No new posts Validate if a Value from the file is ... balacsv2 COBOL Programming 8 Mon Jun 19, 2017 11:52 pm
No new posts Dynamic output file creation in cobol... smileheal COBOL Programming 7 Thu Jun 15, 2017 10:53 pm
No new posts compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am
No new posts XML Parse for large file muralikrishnan_new COBOL Programming 3 Mon Jun 12, 2017 12:50 pm


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