Data type for output file in DB2 SQL

5 pts.
Tags:
DB2 SQL
IBM DB2
SQL
SQL Database
SQL Server
I am using DB2 SQL on the mainframe - not sure of the version - I want to write out a file and transfer it down to the PC - how do I specifiy numeric values for dollar values so that when I bring the file into Microsoft Access the numeric fields come in as numeric and not character?

Software/Hardware used:
DB2 SQL

Answer Wiki

Thanks. We'll let you know when a new response is added.

There is no such thing as “numeric” in a file. A file, whether on a mainframe or on a PC, is just a collection of bytes.

Within certain products ( e.g. DB2, EXCEL ) one can tell the product that a specified field is numeric. But if you save an EXCEL spreadsheet to a file and then look at the file *outside* of EXCEL, there is nothing that signifies that a specific field is numeric.

Its the same sort of thing as defining a record layout in a COBOL (or any other language) program. The file is made up of bytes of data. Only the record layout within the program says that a specific field is numeric.

With that in mind, how do you get “numeric” data as output from DB2 for z/OS. (You said mainframe, so I assume z/OS.) Depending on what program (SQL processor) you are using to access the DB2 data, it is quite likely that the output of such a program is editing the numeric value with zero suppression (which replaces leading zeroes with spaces). And it is probably sticking a decimal point in there also. The decimal point does not exist in the raw data. it is stuck in by the program using the DB2 definition of the data.

Let me emphasize that I believe it is your SQL processor program that is doing this. it is not DB2 nor the SQL itself. For example, if you use SQL embedded in a COBOL program then the result of an SQL query will be placed in Working Storage and indeed the data in that field will have leading zeroes. Normally, zero suppression is used when creating a report. And most dynamic SQL processors are intended to produce reports. That’s why they have headings on the output. And that is why there is a decimal point.

So, you have a few choices.

Maybe the SQL processor you are using has an option to turn off “formatting” in the output.

Write a program to create the file, put your SQL inside the program, and output the file. REXX comes to mind because it would be pretty easy to just use SQL to read DB2 and output to a file.

Use one of the DB2 utilities to create the file. DSNTIAUL comes to mind. But then you will need to reformat the output file to change any binary fields, or packed fields, and such (dates will be in DB2 internal format).

It seems I left out the most obvious approach …

lets say your column containing dollar amounts is named COL1 and is defined as DECIMAL(7,2)

you might SELECT SUBSTR(CHAR(COL1),1,5) || SUBSTR(CHAR(COL1),7,2) …

Those beginning numbers may need to be adjust one position due to the possibility of a sign being returned by the SQL processor.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following