View previous topic :: View next topic
|
Author |
Message |
ravindra.vadali
New User
Joined: 30 Jan 2012 Posts: 34 Location: USA
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
ravindra.vadali
New User
Joined: 30 Jan 2012 Posts: 34 Location: USA
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
You're welcome - good luck
d |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
ravindra.vadali
New User
Joined: 30 Jan 2012 Posts: 34 Location: USA
|
|
|
|
Thanks a many, Sushanth. The code which you suggested worked perfectly for me.
Regards,
Ravi |
|
Back to top |
|
|
|