Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us