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

Louie, David David.Louie at blackrock.com
Thu Jul 12 05:34:45 MST 2018


I think we will go with select count(*) as I don’t think there is a way to get the number of rows extracted.

We will be copying static data on the cutover weekend so there will be no updates/inserts/deletes in flight during the extracts.

Thanks for the input Mark & Kirby.

David


admcdba001{dlouie}:/u1/dlouie=>sqsh -S DEVEWD_IQ -i testcopy.sql
starting copy for table dlouie.testCopy
number for rows in table before copy
col1
-----------
           1

(1 row affected)
settting temp option and running extract
col1
-----------

(0 rows affected)
extract completed
resetting options
number of rows in table after extract is:
count()
--------------------
                    1

(1 row affected)
admcdba001{dlouie}:/u1/dlouie=>

From: Louie, David
Sent: Thursday, July 12, 2018 8:24 AM
To: 'Mark Mumy' <markdmumy at gmail.com>
Cc: iqug at dssolutions.com
Subject: RE: [IQUG] [iqug] Data and rowcounts disappear after setting extract options

Mark,

I checked the log and there is no audit trail of extraction.

David


From: Mark Mumy [mailto:markdmumy at gmail.com]
Sent: Wednesday, July 11, 2018 3:04 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 not in front of my computer.....doesn’t the IQ msg or one of the server log files track it?
Mark

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

Sent from my mobile device

On Jul 11, 2018, at 14:50, Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>> wrote:
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<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]
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/20180712/547b8821/attachment-0001.html>


More information about the IQUG mailing list