Is there a dbisql option for field separator?

[former_member319447]

Hi All,

I have a stored procedure which returns over 500,000 rows, and I want to have a "|" as a field separator.

Do I have to use the OUTPUT syntax, or can I use some dbisql option to do this?

If so, example please? I am using dbisql -nogui.

I really don't want to select the results of the proc into a temp table, and then use OUTPUT. Seems inefficient to me.

Thanks,

[Gisung · Accepted solution]

Hi,

As I know "the isql" has a function for field separator as follows.

Formatting isql Output

• -s colseparator – changes the column separator character. The default is single space.

SyBooks Online

But dbisql does not have a same function.

Please refer to below option for dbisql.

Interactive SQL Command Line Options

SyBooks Online

==

Gi-Sung Jang

[former_member319447]

Ok, thanks you guys. I am surprised that dbisql does not have a column separator option. I've been using isql for a long time, so I guess I'll stick with that for batch report generation.

[former_member319447]

Correction. I am going to use dbisql. Major discovery. When using dbisql and this:

set temporary option temp_extract_name1='p1ClrptDividendIndex.lst'

set temporary option temp_extract_column_delimiter='|'

set temporary option temp_extract_row_delimiter='\n'

The total execution time of the procedure drops from 12 minutes down to 2 minutes. It was taking 10 minutes to transfer the results back up through the client and then out to a file.

[c_baker]

Setting the 'temp_extract' options causes the IQ server to save the result set data directly from the server (bulk export).  The data is not transferred back to the client at all.

Your savings are on the elimination of the network transfer back to the client.  You can use dbisql or isql or any other client in this case.

Setting temp_extract_name1 to anything other than blank will enable extraction (no results sent to clent).  The extraction can fill up to 8 files sequentially, each limited by the temp_extract_sizeN option (OS max file size dependent).

You can extract binary as well this way.

The full list of options is under the following link:

Data Extraction Facility

What you must remember is that the extraction file location must be local to the IQ server, not the client, so will be subject to that file system's performance.

Chris

[jong-kil_park]

Hi Mark,

As Gi-Sung said, there is no option to control the behavior in dbisql command.

So, I think you have to use OUTPUT command instead.

Depending on the number of result set, it would be better for you to use the extraction options like below.

Because the OUTPUT command doesn't add the column delimiter at the end of the each row, you can't take advantage of multi-threaded processing when loading the generated file.

20196143|28324|1994-10-26

20196144|94591|1997-06-05

20196181|98885|1995-09-24

(Of course, there will not be much difference in performance if the number of the result set is not that many.)

create or replace procedure p_partkey()

begin

set temporary option Temp_Extract_Name1='C:\temp\partkey.out';

set temporary option Temp_Extract_Column_Delimiter='|';

set temporary option Temp_Extract_Row_Delimiter='\n';

select l_orderkey, l_suppkey, l_shipdate from lineitem where l_partkey =539067 ;

end ;

Best Regards

Jerry