Antwort: DSNTIAUL: How to create a comma-delimited output file ?

[login to unmask email]

Antwort: DSNTIAUL: How to create a comma-delimited output file ?
Hi Peter,

you can use a select-statement in the following way and the fields in the result
file will be seperated by commas.

'select column1, ',', column2, ',' ... from table;'


greetings
h. schmaelzle



Georg Peter

AW: Antwort: DSNTIAUL: How to create a comma-delimited output fil e ?
(in response to heiko.schmaelzle@SI-BW.DE)
Thank you, Heiko.

We have specified a complete SQL statement in SYSIN. And we gave SYSTSIN
"PARMS(´SQL´)".

And surprise, surprise - it works... ;-))

With kind regards - mit freundlichen Grüssen,
G e o r g H . P e t e r



James Campbell

Re: DSNTIAUL: How to create a comma-delimited output file ?
(in response to Georg Peter)
There are a couple of slights catch with the method below:

1) DSNTIAUL includes lengths on variable length columns. One such variable
length column are the "','" constants. For these, you will need to use
"SUBSTR(',',1,1)" to get fixed length - without the x'0001' length. You
will need to use similar methods on other varchars.

2) Numeric data is output in internal format. If you are on V5 or later,
you could use
CASE WHEN col<0 THEN SUBSTR('-',1,1) ELSE SUBSTR(' ',1,1) END ||
DIGITS(col)
to get a nice character format.

etc.

An alternative is to use Ralph Alcorn's batch SPUFI JCL (he posted it to
DB2-L in the American.edu days, but I've recently reposted it at
http://mvshelp.com/ubb/Forum2/HTML/000279.html). This will handle the
problems noted above.

/* standard disclaimer */
James Campbell
DBA
Hansen Corporation, Doncaster
+61 3 9843 8442
[login to unmask email]

-----Original Message-----
From: Heiko Schmaelzle [mailto:[login to unmask email]
Sent: Friday, December 15, 2000 11:02 PM
To: [login to unmask email]
Subject: [DB2-L] Antwort: DSNTIAUL: How to create a comma-delimited
output file ?


Hi Peter,

you can use a select-statement in the following way and the fields in the
result
file will be seperated by commas.

'select column1, ',', column2, ',' ... from table;'


greetings
h. schmaelzle







**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************



Alan Smith

Re: DSNTIAUL: How to create a comma-delimited output file ?
(in response to James Campbell)
Under V6 you can use CHAR rather than SUBSTRING and DIGITS.

Alan Smith



------------------------------

Date: Sun, 17 Dec 2000 15:03:07 +1100
From: James Campbell <[login to unmask email]>
Subject: Re: DSNTIAUL: How to create a comma-delimited output file ?

There are a couple of slights catch with the method below:

1) DSNTIAUL includes lengths on variable length columns. One such variable
length column are the "','" constants. For these, you will need to use
"SUBSTR(',',1,1)" to get fixed length - without the x'0001' length. You
will need to use similar methods on other varchars.

2) Numeric data is output in internal format. If you are on V5 or later,
you could use
CASE WHEN col<0 THEN SUBSTR('-',1,1) ELSE SUBSTR(' ',1,1) END ||
DIGITS(col)
to get a nice character format.

etc.

An alternative is to use Ralph Alcorn's batch SPUFI JCL (he posted it to
DB2-L in the American.edu days, but I've recently reposted it at
http://mvshelp.com/ubb/Forum2/HTML/000279.html). This will handle the
problems noted above.

/* standard disclaimer */
James Campbell
DBA
Hansen Corporation, Doncaster
+61 3 9843 8442



Phil Castle

Re: DSNTIAUL: How to create a comma-delimited output file ?
(in response to Alan Smith)
if you would consider using a non-mainframe based tool, then there are a
number of other options. The tool I use all the time is one I've written
myself. Not surprisingly it does all the things that I, as a DBA/developer,
would like a tool to do. This has an option to output a table / query as a
comma-delimited file. I use this all the time and it works fine. It has many
options to cope with all the sort of things which can go wrong with an
export-to-file - for instance if any of your char columns contain commas
then you will have problems when you try and load it. My tool can either
enclose the char columns in double quotes, or scan the char columns and
remove the commas from them.

It also has an option to export the table / query as a series of insert
statements - this is the most reliable method I've found of getting data
into another database, especially if it is a different database type (I
should point out that this method is unsuitable if you have a very large
number of rows).

My tool is called Advanced Query Tool and can be downloaded from
www.querytool.com.

Is the DBMS you are importing into DB2 or another database type? If it's
another database type (such as Oracle) then you will have to think about
data conversion issues. For instance there are a lot of variations in the
Date/Time and Timestamp data types between the databases which you will need
to deal with.

Phil Castle.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Alan Smith
Sent: Monday, 18 December 2000 00:40
To: [login to unmask email]
Subject: Re: DSNTIAUL: How to create a comma-delimited output file ?


Under V6 you can use CHAR rather than SUBSTRING and DIGITS.

Alan Smith



------------------------------

Date: Sun, 17 Dec 2000 15:03:07 +1100
From: James Campbell <[login to unmask email]>
Subject: Re: DSNTIAUL: How to create a comma-delimited output file ?

There are a couple of slights catch with the method below:

1) DSNTIAUL includes lengths on variable length columns. One such variable
length column are the "','" constants. For these, you will need to use
"SUBSTR(',',1,1)" to get fixed length - without the x'0001' length. You
will need to use similar methods on other varchars.

2) Numeric data is output in internal format. If you are on V5 or later,
you could use
CASE WHEN col<0 THEN SUBSTR('-',1,1) ELSE SUBSTR(' ',1,1) END ||
DIGITS(col)
to get a nice character format.

etc.

An alternative is to use Ralph Alcorn's batch SPUFI JCL (he posted it to
DB2-L in the American.edu days, but I've recently reposted it at
http://mvshelp.com/ubb/Forum2/HTML/000279.html). This will handle the
problems noted above.

/* standard disclaimer */
James Campbell
DBA
Hansen Corporation, Doncaster
+61 3 9843 8442








Steve Tennant

Re: DSNTIAUL: How to create a comma-delimited output file ?
(in response to Phil Castle)
****************************************************************
IMPORTANT

The information transmitted is for the use of the intended
recipient only and may contain confidential and/or legally
privileged material. Any review, re-transmission, disclosure,
dissemination or other use of, or taking of any action in
reliance upon, this information by persons or entities other
than the intended recipient is prohibited and may result in
severe penalties. If you have received this e-mail in error
please notify the Privacy Hotline of the Australian Taxation
Office, telephone 13 2869 and delete all copies of this
transmission together with any attachments.

****************************************************************
If you have FTP up and running that is another option, it has a couple of
outputs like comma delimited or spreadsheet format. I don't have it here so
I can't check the precise options sorry. Of course this only helps if you
going non m/f.

Steve T

-----Original Message-----
From: Peter, Georg [mailto:[login to unmask email]
Sent: Friday, 15 December 2000 22:38
To: [login to unmask email]
Subject: DSNTIAUL: How to create a comma-delimited output file ?


Hello List.

In a normal use DSNTIAUL creates an output file with it's own format, call
it DSNTIAUL-format.

Now we have the need to create a comma-delimited file that an other DBMS can
accept as input. The goal is to separate each column value per row with a
comma.

Can we create such a comma-delimited output file with DSNTIAUL and if so,
how ? Any examples ?

Thanks in advance for any help you can offer.

With kind regards - mit freundlichen Grüssen,
G e o r g H . P e t e r
DB2 Data Base Administrator c/o
-------------------------------------------------------------------
Datenzentrale Baden-Wuerttemberg
Technology & Development Center
Knowlegde Center Database Systems
Krailenshaldenstrasse 44, 70469 Stuttgart, Germany
e:mail [login to unmask email]
Phone 0049-711-8108-271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.de
----------------------------------------------------------------------

"Experience is something you don't get until after you need it." - Anonymous



http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]



Kevin K

Re: DSNTIAUL: How to create a comma-delimited output file ?
(in response to Steve Tennant)
****************************************************************
IMPORTANT

The information transmitted is for the use of the intended
recipient only and may contain confidential and/or legally
privileged material. Any review, re-transmission, disclosure,
dissemination or other use of, or taking of any action in
reliance upon, this information by persons or entities other
than the intended recipient is prohibited and may result in
severe penalties. If you have received this e-mail in error
please notify the Privacy Hotline of the Australian Taxation
Office, telephone 13 2869 and delete all copies of this
transmission together with any attachments.

****************************************************************
If you have FTP up and running that is another option, it has a couple of
outputs like comma delimited or spreadsheet format. I don't have it here so
I can't check the precise options sorry. Of course this only helps if you
going non m/f.

Steve T

-----Original Message-----
From: Peter, Georg [[login to unmask email]
Sent: Friday, 15 December 2000 22:38
To: [login to unmask email]
Subject: DSNTIAUL: How to create a comma-delimited output file ?


Hello List.

In a normal use DSNTIAUL creates an output file with it's own format, call
it DSNTIAUL-format.

Now we have the need to create a comma-delimited file that an other DBMS can
accept as input. The goal is to separate each column value per row with a
comma.

Can we create such a comma-delimited output file with DSNTIAUL and if so,
how ? Any examples ?

Thanks in advance for any help you can offer.

With kind regards - mit freundlichen Grüssen,
G e o r g H . P e t e r
DB2 Data Base Administrator c/o
-------------------------------------------------------------------
Datenzentrale Baden-Wuerttemberg
Technology & Development Center
Knowlegde Center Database Systems
Krailenshaldenstrasse 44, 70469 Stuttgart, Germany
e:mail [login to unmask email]
Phone 0049-711-8108-271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.de
----------------------------------------------------------------------

"Experience is something you don't get until after you need it." - Anonymous



http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]

the

Powered by the Email PIM - Info Select - www.miclog.com



[login to unmask email]

Re: DSNTIAUL: How to create a comma-delimited output file ?
(in response to Kevin K)
Hi,

I use this query with delimiters to feed some Excel spreadsheets (we run DB2
V4, OS/390) to follow up the size of my tables:

********************************* Top of Data *******************
SELECT
';'||SUBSTR(A.CREATOR,1,4)||';'||SUBSTR(A.NAME,1,8)||';',
';'||SUBSTR(DIGITS(B.PARTITION),4,2)||';'||DIGITS(B.CARD)||';',
';'||DIGITS(A.RECLENGTH+8)||';'
FROM SYSIBM.SYSTABLES A,
SYSIBM.SYSTABLEPART B
WHERE A.CREATOR LIKE 'ssid%'
AND A.TYPE = 'T'
AND A.DBNAME NOT LIKE '____01_%'
AND A.DBNAME = B.DBNAME
AND A.TSNAME = B.TSNAME
ORDER BY 1, 2
;
******************************** Bottom of Data *****************

Hope this helps,
Regards,

LUONG The-Thanh
<mailto: [login to unmask email]>

===== Opinions are strictly my own - All Disclaimers apply =====



-----Original Message-----
From: Peter, Georg [mailto:[login to unmask email]
Sent: Friday, 15 December, 2000 12:38 PM
To: [login to unmask email]
Subject: DSNTIAUL: How to create a comma-delimited output file ?


Hello List.

In a normal use DSNTIAUL creates an output file with it's own format, call
it DSNTIAUL-format.

Now we have the need to create a comma-delimited file that an other DBMS can
accept as input. The goal is to separate each column value per row with a
comma.

Can we create such a comma-delimited output file with DSNTIAUL and if so,
how ? Any examples ?

Thanks in advance for any help you can offer.

With kind regards - mit freundlichen Grüssen,
G e o r g H . P e t e r
DB2 Data Base Administrator c/o
-------------------------------------------------------------------
Datenzentrale Baden-Wuerttemberg
Technology & Development Center
Knowlegde Center Database Systems
Krailenshaldenstrasse 44, 70469 Stuttgart, Germany
e:mail [login to unmask email]
Phone 0049-711-8108-271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.de
----------------------------------------------------------------------

"Experience is something you don't get until after you need it." - Anonymous



http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]



RICK (SBCSI) DAVIS

Re: DSNTIAUL: How to create a comma-delimited output file ?
(in response to The-Thanh.Luong@AGF.BE)
The-Thanh Luong,
Please use a internet supported character set -- I never open
attachments from listservers and would hate to think I'm missing something
important.

Regards,
Rick Davis

"This e-mail and any files transmitted with it are the property of SBC,
are confidential, and are intended solely for the use of the individual
or entity to whom this e-mail is addressed. If you are not one of the
named recipient(s) or otherwise have reason to believe that you have
received this message in error, please notify the sender at 314-235-6854
and delete this message immediately from your computer. Any other use,
retention, dissemination, forwarding, printing, or copying of this
e-mail is strictly prohibited."

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Monday, December 18, 2000 2:23 AM
To: [login to unmask email]
Subject: Re: DSNTIAUL: How to create a comma-delimited output file ?


This message uses a character set that is not supported by the Internet
Service. To view the original message content, open the attached message.
If the text doesn't display correctly, save the attachment to disk, and then
open it using a viewer that can display the original character set.



[login to unmask email]

Re: DSNTIAUL: How to create a comma-delimited output file ?
(in response to RICK (SBCSI) DAVIS)
Hi Rick,

Sorry for any inconvenience my previous post may have caused.

In my SELECT statement, I use the characters "vertical bar" to concatenate
my fields unloaded by DSNTIAUL, including my semi-colon delimiters. There
are also underscores in my DBNAME LIKE clause. The rest of the query is
quite normal, I think.

I usually send messages in plain text format, sometimes in RTF, never in
HTML. Besides, I am not an Internet fanatic, so I don't know which are the
Internet-supported character sets. If you have the answer, please let me
know.

Regards,
The-Thanh
<mailto: [login to unmask email]>

===== Opinions are strictly my own - All Disclaimers apply =====



-----Original Message-----
From: DAVIS, RICK (SBCSI) [mailto:[login to unmask email]
Sent: Monday, 18 December, 2000 2:15 PM
To: [login to unmask email]
Subject: Re: DSNTIAUL: How to create a comma-delimited output file ?


The-Thanh Luong,
Please use a internet supported character set -- I never open
attachments from listservers and would hate to think I'm missing something
important.

Regards,
Rick Davis

"This e-mail and any files transmitted with it are the property of SBC,
are confidential, and are intended solely for the use of the individual
or entity to whom this e-mail is addressed. If you are not one of the
named recipient(s) or otherwise have reason to believe that you have
received this message in error, please notify the sender at 314-235-6854
and delete this message immediately from your computer. Any other use,
retention, dissemination, forwarding, printing, or copying of this
e-mail is strictly prohibited."

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Monday, December 18, 2000 2:23 AM
To: [login to unmask email]
Subject: Re: DSNTIAUL: How to create a comma-delimited output file ?


This message uses a character set that is not supported by the Internet
Service. To view the original message content, open the attached message.
If the text doesn't display correctly, save the attachment to disk, and then
open it using a viewer that can display the original character set.








RICK (SBCSI) DAVIS

Re: DSNTIAUL: How to create a comma-delimited output file ?
(in response to The-Thanh.Luong@AGF.BE)
The-Thanh, whatever character set you used below worked just fine, thank
you.

Regards,
Rick Davis

"This e-mail and any files transmitted with it are the property of SBC,
are confidential, and are intended solely for the use of the individual
or entity to whom this e-mail is addressed. If you are not one of the
named recipient(s) or otherwise have reason to believe that you have
received this message in error, please notify the sender at 314-235-6854
and delete this message immediately from your computer. Any other use,
retention, dissemination, forwarding, printing, or copying of this
e-mail is strictly prohibited."

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Tuesday, December 19, 2000 1:31 AM
To: [login to unmask email]
Subject: Re: DSNTIAUL: How to create a comma-delimited output file ?

Hi Rick,

Sorry for any inconvenience my previous post may have caused.

In my SELECT statement, I use the characters "vertical bar" to concatenate
my fields unloaded by DSNTIAUL, including my semi-colon delimiters. There
are also underscores in my DBNAME LIKE clause. The rest of the query is
quite normal, I think.

I usually send messages in plain text format, sometimes in RTF, never in
HTML. Besides, I am not an Internet fanatic, so I don't know which are the
Internet-supported character sets. If you have the answer, please let me
know.

Regards,
The-Thanh
<mailto: [login to unmask email]>

===== Opinions are strictly my own - All Disclaimers apply =====

-----Original Message-----
From: DAVIS, RICK (SBCSI) [mailto:[login to unmask email]
Sent: Monday, 18 December, 2000 2:15 PM
To: [login to unmask email]
Subject: Re: DSNTIAUL: How to create a comma-delimited output file ?

The-Thanh Luong,
Please use a internet supported character set -- I never open
attachments from listservers and would hate to think I'm missing something
important.

Regards,
Rick Davis

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Monday, December 18, 2000 2:23 AM
To: [login to unmask email]
Subject: Re: DSNTIAUL: How to create a comma-delimited output file ?

This message uses a character set that is not supported by the Internet
Service. To view the original message content, open the attached message.
If the text doesn't display correctly, save the attachment to disk, and then
open it using a viewer that can display the original character set.