[IQUG] [iqug] Data and rowcounts disappear after setting extract options

Louie, David David.Louie at blackrock.com
Wed Jul 11 11:50:01 MST 2018


IQ_SERVER.dlouie.master.0.1> select * from dlouie.testCopy
col1
-----------
           1
(1 row affected)
IQ_SERVER.dlouie.master.0.1> set TEMPORARY OPTION Temp_Extract_Binary=ON
IQ_SERVER.dlouie.master.0.1> set TEMPORARY OPTION TEMP_EXTRACT_SIZE1=4294967296
IQ_SERVER.dlouie.master.0.1> set TEMPORARY OPTION Temp_Extract_Directory='/dbbackups'
IQ_SERVER.dlouie.master.0.1> set TEMPORARY OPTION Temp_Extract_Name1 = 'dlouie.testCopy.0.txt'
IQ_SERVER.dlouie.master.0.1> select * from dlouie.testCopy
col1
-----------
(0 rows affected)
IQ_SERVER.dlouie.master.0.1> set TEMPORARY OPTION Temp_Extract_Binary=OFF
IQ_SERVER.dlouie.master.0.1> set TEMPORARY OPTION Temp_Extract_Name1 = ''
IQ_SERVER.dlouie.master.0.1> set TEMPORARY OPTION TEMP_EXTRACT_SIZE1=0
IQ_SERVER.dlouie.master.0.1> set TEMPORARY OPTION TEMP_EXTRACT_NAME1=''
IQ_SERVER.dlouie.master.0.1> select @@rowcount
@@rowcount
-----------
           0
(1 row affected)
IQ_SERVER.dlouie.master.0.1> commit
IQ_SERVER.dlouie.master.0.1> select * from dlouie.testCopy
col1
-----------
           1
(1 row affected)
IQ_SERVER.dlouie.master.0.1> select @@rowcount
@@rowcount
-----------
           1
(1 row affected)


Got it!  Thanks!


So the only way to get a rowcount which has been extracted to the flat file would be to do a select count(*) in the batch?

David




From: Mark Mumy [mailto:markdmumy at gmail.com]
Sent: Wednesday, July 11, 2018 2:28 PM
To: Louie, David <David.Louie at blackrock.com>
Cc: iqug at dssolutions.com
Subject: Re: [IQUG] [iqug] Data and rowcounts disappear after setting extract options


[EXTERNAL EMAIL]
You never unset the extraction options.
Mark

========================

Sent from my mobile device

On Jul 11, 2018, at 14:14, Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>> wrote:
Thanks Mark. That would certainly explain  the (rows affected 0)

Why would our 2nd select * in the batch return no rows?

Thanks
David


From: Mark Mumy [mailto:markdmumy at gmail.com]
Sent: Wednesday, July 11, 2018 1:27 PM
To: Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>>
Cc: iqug at dssolutions.com<mailto:iqug at dssolutions.com>
Subject: Re: [IQUG] [iqug] Data and rowcounts disappear after setting extract options


[EXTERNAL EMAIL]
I’m pretty sure that the rowcount is computed as rows are being returned to the client and not selected from the table.  On an extract the file is written directly by IQ and doesn’t go through the SA engine and client.
Mark

========================

Sent from my mobile device

On Jul 11, 2018, at 13:07, Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>> wrote:
IQ_SERVER.dlouie..2001.1> select * from dlouie.testCopy
IQ_SERVER.dlouie..2001.2> ;
col1
-----------
           1

(1 row affected)
IQ_SERVER.dlouie..2002.1> select @@rowcount
IQ_SERVER.dlouie..2002.2> ;
@@rowcount
-----------
           1

(1 row affected)
IQ_SERVER.dlouie..2003.1> set TEMPORARY OPTION Temp_Extract_Binary=ON
IQ_SERVER.dlouie..2003.2> ;
IQ_SERVER.dlouie..2004.1> set TEMPORARY OPTION TEMP_EXTRACT_SIZE1=4294967296
IQ_SERVER.dlouie..2004.2> ;
IQ_SERVER.dlouie..2005.1> set TEMPORARY OPTION Temp_Extract_Directory='/dbbackups'
IQ_SERVER.dlouie..2005.2> ;
IQ_SERVER.dlouie..2006.1> set TEMPORARY OPTION Temp_Extract_Name1 = 'dlouie.testCopy.0.txt'
IQ_SERVER.dlouie..2006.2> ;
IQ_SERVER.dlouie..2007.1> select * from dlouie.testCopy
IQ_SERVER.dlouie..2007.2> ;
col1
-----------

(0 rows affected)
IQ_SERVER.dlouie..2008.1>  select @@rowcount
IQ_SERVER.dlouie..2008.2> ;
@@rowcount
-----------
           0

(1 row affected)
IQ_SERVER.dlouie..2009.1> select * from dlouie.testCopy
IQ_SERVER.dlouie..2009.2> ;
col1
-----------

(0 rows affected)
IQ_SERVER.dlouie..2010.1> select @@rowcount
IQ_SERVER.dlouie..2010.2> ;
@@rowcount
-----------
           0

(1 row affected)
IQ_SERVER.dlouie..2011.1>



We are creating a script to extract data to flat files for our solaris to linux migration.


1)     There seems to be no way to get the number or rows extracted as IQ returns 0 rows after the select executes thus @@rowcount = 0.   Is there no way to get the extracted number of rows to the flat file?

2)     After the select executes in the batch when I do a select * from the table I get 0 rows and @@rowcount 0 even though there is 1 row in the table.   Why does this happen?


Thanks
David


This message may contain information that is confidential or privileged. If you are not the intended recipient, please advise the sender immediately and delete this message. See http://www.blackrock.com/corporate/en-us/compliance/email-disclaimers for further information.  Please refer to http://www.blackrock.com/corporate/en-us/compliance/privacy-policy for more information about BlackRock’s Privacy Policy.
For a list of BlackRock's office addresses worldwide, see http://www.blackrock.com/corporate/en-us/about-us/contacts-locations.

© 2018 BlackRock, Inc. All rights reserved.
_______________________________________________
IQUG mailing list
IQUG at iqug.org<mailto:IQUG at iqug.org>
http://iqug.org/mailman/listinfo/iqug<https://urldefense.proofpoint.com/v2/url?u=http-3A__iqug.org_mailman_listinfo_iqug&d=DwMFaQ&c=zUO0BtkCe66yJvAZ4cAvZg&r=hvVkP5y30w3_fHBwXsoZ2pqbwQXietniE01Z666b0tE&m=UrMN84psGZ_tOTBK6Oo6i_94PGRfD0vokAyeJxTZ7LA&s=ZKBzrdKZ4RWpfHgubBV39EnPYfwottSfaZakNjHeH00&e=>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20180711/77b1df11/attachment-0001.html>


More information about the IQUG mailing list