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 to get the column names of the table using BMC unload

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

New User


Joined: 30 Jan 2012
Posts: 34
Location: USA

PostPosted: Wed Aug 01, 2012 8:56 pm    Post subject: How to get the column names of the table using BMC unload
Reply with quote

Hi All,

Good morning!! I have a small requirement, I need to unload the data in the table to a .csv file. I wrote a BMC unload card and accomplished that. But then the client wants me to also have the column names of the table to be imported to that .csv file.

I tried the AS clause along with the select in the BMC unload utility but that didn't help me much (sample of my code is stated below)

Code:
UNLOAD                                   
   DIRECT NO                             
   FORMAT CSV                           
   TERMINATEDBY '~'                     
   ENCLOSEDBY ''                         
   NULLSTRING ''                         
   TSFMT('YYYY-MM-DD-HH.MM.SS.NNNNNN')   
   TIMEFMT('HH.MM.SS')                   
  SELECT EMP_NAME AS EMPLOYEE_NAME
      , JOINE_DTM     AS JOIN_DATE       
----
-----
FROM  EMPLOYEE_TABLE  WITH UR;                   


I was thinking of typing in the column names in a different file and then concatenate that file to the extract dump file but before that I wanted to check in this forum if we could achieve the same using BMC unload utility.

My sincere apologies if my requirement is incomplete or if I mis stated anything, I will provide more details if required.

Thanks a many in advance for the help.

Regards,
Ravi

Code'd
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 01, 2012 9:25 pm    Post subject:
Reply with quote

Hello,

One way would be to consider writing a query that will extract the column names from db2 and writing these into a variable length delimited file. Then unload the data into a different variable length delimited file. Then concatenate the 2 into a single output file and send it wherever it needs to go.
Back to top
View user's profile Send private message
ravindra.vadali

New User


Joined: 30 Jan 2012
Posts: 34
Location: USA

PostPosted: Thu Aug 02, 2012 9:01 am    Post subject:
Reply with quote

Thanks for directing me on this, I will try this suggested method and will share the results in this post.

Once again many thanks for 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: Thu Aug 02, 2012 6:54 pm    Post subject: Reply to: How to get the column names of the table using BMC
Reply with quote

You're welcome - good luck icon_smile.gif

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

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Aug 03, 2012 4:30 pm    Post subject:
Reply with quote

Ravi,

You can also try like this, BMC should conside this as two SQL statements and they can be unloaded to the same dataset.

Code:
  UNLOAD                                                 
    DIRECT NO                                             
*                                                         
  SELECT CHAR('EMPLOYEE_NAME'),CHAR('JOIN_DATE')
  .........
  FROM SYSIBM.SYSDUMMY1                                   
  WITH UR;                                               
*                                                         
SELECT EMP_NAME AS EMPLOYEE_NAME
      , JOINE_DTM     AS JOIN_DATE       
----
-----
FROM  EMPLOYEE_TABLE  WITH UR;


When you run the job, you will see like below in the SYSPRINT, first statement will SYSDUMMY1 and second will be your SQL statement.
Code:
BMC51676I UNLOAD STATISTICS:  1 ROWS PROCESSED FOR SELECT STATEMENT NO. 1, 0 DIS
BMC51676I UNLOAD STATISTICS:  # ROWS PROCESSED FOR SELECT STATEMENT NO. 2, 0 DIS
BMC51674I UNLOAD STATISTICS:  ## RECORDS WRITTEN TO DDNAME 'SYSREC'             
BMC51675I UNLOAD STATISTICS:  0 RECORDS DISCARDED DUE TO ERRORS                 

Thanks,
Sushanth
Back to top
View user's profile Send private message
ravindra.vadali

New User


Joined: 30 Jan 2012
Posts: 34
Location: USA

PostPosted: Fri Aug 03, 2012 6:58 pm    Post subject:
Reply with quote

Thanks a many, Sushanth. The code which you suggested worked perfectly for me.

Regards,
Ravi
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 unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
This topic is locked: you cannot edit posts or make replies. Need help in estimating space of unlo... ashek15 IMS DB/DC 12 Fri Apr 07, 2017 5:11 am
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm


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