[IQUG] IQUG Digest, Vol 67, Issue 33

Steve Shen sshen at sscinc.com
Fri Jul 13 13:15:05 MST 2018


Of course I can always check the error code and decide what to do.  I am providing one example below

exec sp_iqrebuildindex '<owner>.<TableName>' ,'column <ColumnName>'; if (@@error=0) commit else rollback;

There will be many thousands of SQL codes like above per SQL batch; but it may difficult for me to tell whether the execution per index rebuild is successful or failing.

I may have to add more codes into each line of the SQL batch; but that will make the SQL clumsy.

Regards,

Steve Shen

-----Original Message-----
From: Steve Shen
Sent: Friday, July 13, 2018 3:49 PM
To: iqug at iqug.org; 'Mumy, Mark'
Subject: RE: IQUG Digest, Vol 67, Issue 33

Hi Mark,

These general guidelines in determining how to set up the value on "-gn" is very helpful even though I was not the person that raised the issue.  Thank you.

I am adding two more topics for this commuity to discuss:

Topics:
        1. Does IQ know to commit or to roll back for any given transaction using "dbisql" with "-onerror continue"?
        2. It took around 17 hours in rebulding all the FP indexes concurrently with some failures.

I am going to migrate one UAT IQ server from version 15.4 to 16.x later tonight and tomorrow.  There are more than 1.2 million FP indexes for me to rebuild after it's migrated to version 16.x.

I will submit multiple cron jobs to rebuild multiple indexes concurrently.

While running "dbisql" from the front-end, it would prompt me to stop or to contine whenever it failed. I noticed that some index rebuilds failed while running "dbisql" from the frond-end.  I have not got the time in identifying what caused the failures.

If I specify "-onerror continue" on the Shell script that executs "dbisql", will IQ know to commit when the index rebuid is good and to roll back automatically when the index rebuild fails?

Is there any way to cut short the long duration on rebuilding all the FP indexes?  I raise this concerns because I will have to handle more than 1.7 miliion FP indexes for the incoming production migration.

Thank you very much.

Kind regards,

Steve
-----Original Message-----
From: iqug-bounces at iqug.org [mailto:iqug-bounces at iqug.org] On Behalf Of iqug-request at iqug.org
Sent: Thursday, July 12, 2018 10:27 AM
To: iqug at iqug.org
Subject: IQUG Digest, Vol 67, Issue 33

Send IQUG mailing list submissions to
        iqug at iqug.org

To subscribe or unsubscribe via the World Wide Web, visit
        http://iqug.org/mailman/listinfo/iqug
or, via email, send a message with subject or body 'help' to
        iqug-request at iqug.org

You can reach the person managing the list at
        iqug-owner at iqug.org

When replying, please edit your Subject line so it is more specific than "Re: Contents of IQUG digest..."


Today's Topics:

   1. Re: [CAUTION] Re:  [CAUTION] Re:  SQLtrace (Mumy, Mark)


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

Message: 1
Date: Thu, 12 Jul 2018 14:26:30 +0000
From: "Mumy, Mark" <mark.mumy at sap.com>
To: "Blyakher, Alexander (CWM-NR)" <alexander.blyakher at rbccm.com>
Cc: "iqug at iqug.org" <iqug at iqug.org>,    Alexander Blyakher
        <alexblyakher at yahoo.com>
Subject: Re: [IQUG] [CAUTION] Re:  [CAUTION] Re:  SQLtrace
Message-ID: <B7BEA2FC-83C4-4427-A580-E232885541C2 at sap.com>
Content-Type: text/plain; charset="utf-8"

I reversed the -gm/-gn:

When setting the -gm option to set the total number of concurrent logins, it is critical to also set the -gn option.  -gn is not set so it defaults to -gm + 5.  Given that -gm is 400, this means that IQ will only allocate 300 threads for the catalog, all threads will be reserved for network connectivity with none left over for any catalog work.

In normal situations, -gn should be set to 1.x the value of -gm.  In this case, with -gm set to 400, -gn should be set to 600.

As a general rule, follow the guidelines below for the -gm and -gn settings:
?    If -gm is less than 100, set -gn = -gm + 50
?    If -gm is >= 100, set -gn = -gm * 1.5
?    On a coordinator node, set -gn to (-gm + 50 + number_of_iq_nodes)
?    On a coordinator, high user concurrency is not needed for external connections.  I would recommend starting with a -gm of 25 and -gn of 99 ( 25 + 50 + 24) or rounded to 100.

It is of vital importance to understand what happens when setting -gm too high or too low.  This option reserves resources at startup so that the connections will always succeed.  It is important to set -gm to a value that will meet the peak workload of the system and that the peak be reasonable.  Setting the number of connections to such a high value, I consider anything over 200 high, will cause a significant amount of threads and resources to sit idle until that very infrequent time when you actually get that number of concurrent users.

The point is be realistic about these values so that they don?t adversely affect performance.  More threads, in any application, simply means more overhead at the OS level to manage those threads.  Add to that, IQ reserves threads for specific use, in this case that means a massive amount of threads (600) that could be sitting idle most of the time but yet still need to be maintained.

Mark

Mark Mumy
Strategic Technology Incubation Group
SAP Global Center of Excellence |  SAP
M +1 347-820-2136 | E mark.mumy at sap.com<mailto:mark.mumy at sap.com>
My Blogs: https://blogs.sap.com/author/markmumy/

https://sap.na.pgiconnect.com/I825063
Conference tel: 18663127353,,8035340905#

From: Mark Mumy <mark.mumy at sap.com>
Date: Thursday, July 12, 2018 at 8:36 AM
To: "Blyakher, Alexander (CWM-NR)" <alexander.blyakher at rbccm.com>
Cc: Alexander Blyakher <alexblyakher at yahoo.com>, Chris Baker <c.baker at sap.com>, "iqug at iqug.org" <iqug at iqug.org>
Subject: Re: [CAUTION] Re: [IQUG] [CAUTION] Re: SQLtrace

Not relaterd to your issue?

Unless you were told to do so by support, you need to remove -gt and -gtc as these can greatly throttle your performance.  Also, you -gm and -gn ratios are off.  -gm should be 1.5x the value of -gn.  The IQ 16 packet size is now 64000, so increase -p from 16000 to the larger value for better performance.

Mark

Mark Mumy
Strategic Technology Incubation Group
SAP Global Center of Excellence |  SAP
M +1 347-820-2136 | E mark.mumy at sap.com<mailto:mark.mumy at sap.com>
My Blogs: https://blogs.sap.com/author/markmumy/

https://sap.na.pgiconnect.com/I825063
Conference tel: 18663127353,,8035340905#

From: "Blyakher, Alexander (CWM-NR)" <alexander.blyakher at rbccm.com>
Date: Wednesday, July 11, 2018 at 11:41 AM
To: Mark Mumy <mark.mumy at sap.com>
Cc: Alexander Blyakher <alexblyakher at yahoo.com>, Chris Baker <c.baker at sap.com>, "iqug at iqug.org" <iqug at iqug.org>
Subject: RE: [CAUTION] Re: [IQUG] [CAUTION] Re: SQLtrace

Executable is a binary from an external team. The same binary is used to process data on both servers.

IQ16 cfg
-n P37
#
# Port Number
# UAT 168xx
# QA 167xx
# DEV 166xx
# PROD 165xx
#
-x tcpip{port=xxxx}
-c 512M
#-c 48M
-gc 20
# Production 20/24 CPUs Dev 10/12 CPUs
-iqnumbercpus 12
-gd all
-gl all
-gm 400
# ADDED -gn 10/1/2014
-gn 300
-gp 4096
-iqlm 50000
-iqmc 50000
-iqtc 50000
-p 16000
-ti 4400
-o /opt/sybase/p37/home/log/P37.log
# -gm 1 -iqro 1
-zt
-gt 30
-gtc 10
-iqrlvmem 2048
-zr all


IQ15 cfg
-n P45
#
# Port Number
# QA 167xx
# DEV 166xx
# PROD 165xx
#
-x tcpip{host=xxxxxx.ny.xxxx.com;port=xxxxx}
-c 512M
#-c 48M
-gc 20
# Production 20/24 CPUs Dev 10/12 CPUs
-iqnumbercpus 8
-gd all
-gl all
-gm 200
-gp 4096
-iqmc 25000
-iqtc 25000
-p 16000
-ti 4400
-o /opt/sybase/p37/home/log/P37.log
-iqmsgnum 5
-iqmsgsz 100
-zr all

Options set at login
I. 07/10 03:30:14. [,24203,sp_login_environment,4,call dbo.sp_tsql_environment() I. 07/10 03:30:14. [,24203,sp_tsql_environment,7,set temporary option ansinull = 'OFF'
I. 07/10 03:30:14. [,24203,sp_tsql_environment,8,set temporary option tsql_variables = 'ON'
I. 07/10 03:30:14. [,24203,sp_tsql_environment,9,set temporary option ansi_blanks = 'ON'
I. 07/10 03:30:14. [,24203,sp_tsql_environment,10,set temporary option chained = 'OFF'
I. 07/10 03:30:14. [,24203,sp_tsql_environment,11,set temporary option quoted_identifier = 'OFF'
I. 07/10 03:30:14. [,24203,sp_tsql_environment,12,set temporary option allow_nulls_by_default = 'OFF'
I. 07/10 03:30:14. [,24203,sp_tsql_environment,13,set temporary option on_tsql_error = 'CONTINUE'
I. 07/10 03:30:14. [,24203,sp_tsql_environment,14,set temporary option isolation_level = '1'
I. 07/10 03:30:14. [,24203,sp_tsql_environment,15,set temporary option date_format = 'YYYY-MM-DD'
I. 07/10 03:30:14. [,24203,sp_tsql_environment,16,set temporary option timestamp_format = 'YYYY-MM-DD HH:NN:SS.SSS'
I. 07/10 03:30:14. [,24203,sp_tsql_environment,17,set temporary option time_format = 'HH:NN:SS.SSS'
I. 07/10 03:30:14. [,24203,sp_tsql_environment,18,set temporary option date_order = 'MDY'
I. 07/10 03:30:14. [,24203,sp_tsql_environment,19,set temporary option escape_character = 'OFF'


Sasha Blyakher
Compliance Ops IT
(212) 437-9244

Privileged and Confidential. Prepared at the request of counsel. For internal use only

From: Alexander Blyakher [mailto:alexblyakher at yahoo.com]
Sent: Wednesday,July 11, 2018 10:20 AM
To: Blyakher, Alexander (CWM-NR) <alexander.blyakher at rbccm.com>
Subject: Fwd: [CAUTION] Re: [IQUG] [CAUTION] Re: SQLtrace


Sent from my iPhone

Begin forwarded message:
From: "Mumy, Mark" <mark.mumy at sap.com<mailto:mark.mumy at sap.com>>
Date: July 11, 2018 at 8:59:17 AM EDT
To: Alexander Blyakher <alexblyakher at yahoo.com<mailto:alexblyakher at yahoo.com>>, "Baker, Chris" <c.baker at sap.com<mailto:c.baker at sap.com>>
Cc: "iqug at iqug.org<mailto:iqug at iqug.org>" <iqug at iqug.org<mailto:iqug at iqug.org>>
Subject: Re: [CAUTION] Re: [IQUG] [CAUTION] Re:  SQLtrace Can you send a copy of your cfg file?  Also, are you running any of the SQL commands to change the request logging features?  There are two ways to enable and disable this.  You can do it via the config file or you can do it via SQL.  Sounds like there?s something overriding your config file.

Mark

Mark Mumy
Strategic Technology Incubation Group
SAP Global Center of Excellence |  SAP
M +1 347-820-2136 | E mark.mumy at sap.com<mailto:mark.mumy at sap.com>
My Blogs: https://blogs.sap.com/author/markmumy/

https://sap.na.pgiconnect.com/I825063
Conference tel: 18663127353,,8035340905#

From: Alexander Blyakher <alexblyakher at yahoo.com<mailto:alexblyakher at yahoo.com>>
Date: Tuesday, July 10, 2018 at 2:20 PM
To: Chris Baker <c.baker at sap.com<mailto:c.baker at sap.com>>
Cc: "iqug at iqug.org<mailto:iqug at iqug.org>" <iqug at iqug.org<mailto:iqug at iqug.org>>, Mark Mumy <mark.mumy at sap.com<mailto:mark.mumy at sap.com>>
Subject: Re: [CAUTION] Re: [IQUG] [CAUTION] Re: SQLtrace

No, since it wasn?t an upgrade in place. It?s a new server and data was copied over (not with database restore).
Sent from my iPhone

On Jul 10, 2018, at 1:21 PM, Baker, Chris <c.baker at sap.com<mailto:c.baker at sap.com>> wrote:
Did you run ?alter database upgrade? after the upgrade?

Chris
Chris Baker | Database Engineering Evangelist | CERT | PI HANA Platform Data Management | SAP T +1 416-226-7033<tel:+1%20416-226-7033> | M +1 647-224-2033<tel:+1%20647-224-2033> | TF +1 866-716-8860<tel:+1%20866-716-8860>
SAP Canada Inc. 445 Wes Graham Way, Waterloo, N2L 6R2<x-apple-data-detectors://17/1>
c.baker at sap.com<mailto:c.baker at sap.com> | www.sap.com<http://www.sap.com/>

https://sap.na.pgiconnect.com/I826572
Conference tel: 1-866-312-7353,,9648565377#<tel:1-866-312-7353,,9648565377%23>

On Jul 10, 2018, at 1:07 PM, Mumy, Mark <mark.mumy at sap.com<mailto:mark.mumy at sap.com>> wrote:
I'm running IQ 16.1, but that's not what I see:

1> select @@version
2> select property('RequestLogging')
3> go
@@version
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SAP IQ/16.1.020.552/10613/P/sp02.02/Linux/Linux64 - x86_64 - 2.6.18-194/64bit/2017-10-13 11:11:32

(1 row affected)
property('RequestLogging')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALL




Mark

Mark Mumy
Strategic Technology Incubation Group
SAP Global Center of Excellence |  SAP
M +1 347-820-2136 | E mark.mumy at sap.com<mailto:mark.mumy at sap.com>
My Blogs: https://blogs.sap.com/author/markmumy/

https://sap.na.pgiconnect.com/I825063
Conference tel: 18663127353,,8035340905#

On 7/9/18, 11:01 AM, "Alexander Blyakher" <alexblyakher at yahoo.com<mailto:alexblyakher at yahoo.com>> wrote:

   Well. When server is started with -zr All on 15.4, query select property(?RequestLogging?) comes returns ?ALL?, on 16 it comes back with ?SQL?.

   Sent from my iPhone





On Jul 9, 2018, at 10:40 AM, Mumy, Mark <mark.mumy at sap.com<mailto:mark.mumy at sap.com>> wrote:

Or are you referring to the shortened SA plans that it can generate?

Mark

Mark Mumy
Strategic Technology Incubation Group
SAP Global Center of Excellence |  SAP
M +1 347-820-2136 | E mark.mumy at sap.com<mailto:mark.mumy at sap.com>
My Blogs: https://blogs.sap.com/author/markmumy/

https://sap.na.pgiconnect.com/I825063
Conference tel: 18663127353,,8035340905#

On 7/9/18, 9:38 AM, "iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org> on behalf of Mumy, Mark" <iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org> on behalf of mark.mumy at sap.com<mailto:mark.mumy at sap.com>> wrote:

   -zr won't generate a plan.  That comes from using IQ set options like query_plan_as_html and query_plan_after_run.

  Mark

  Mark Mumy
  Strategic Technology Incubation Group
  SAP Global Center of Excellence |  SAP
  M +1 347-820-2136 | E mark.mumy at sap.com<mailto:mark.mumy at sap.com>
  My Blogs: https://blogs.sap.com/author/markmumy/

  https://sap.na.pgiconnect.com/I825063
  Conference tel: 18663127353,,8035340905#

  On 7/9/18, 9:27 AM, "iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org> on behalf of Alexander Blyakher" <iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org> on behalf of alexblyakher at yahoo.com<mailto:alexblyakher at yahoo.com>> wrote:

      After 15.4 to 16 upgrade we?ve noticed that enabling logging with -zr ALL option on 16 fails to capture detailed query info, there is no plan and even sql is abbreviated or hidden for some commands.

      Sent from my iPhone
      _______________________________________________
      IQUG mailing list
      IQUG at iqug.org<mailto:IQUG at iqug.org>
      http://iqug.org/mailman/listinfo/iqug

  _______________________________________________
  IQUG mailing list
  IQUG at iqug.org<mailto:IQUG at iqug.org>
  http://iqug.org/mailman/listinfo/iqug




_______________________________________________
IQUG mailing list
IQUG at iqug.org<mailto:IQUG at iqug.org>
http://iqug.org/mailman/listinfo/iqug

________________________________________

This E-Mail (including any attachments) may contain privileged or confidential information.  It is intended only for the addressee(s) indicated above.

The sender does not waive any of its rights, privileges or other protections respecting this information.

Any distribution, copying or other use of this E-Mail or the information it contains, by other than an intended recipient, is not sanctioned and is prohibited.

If you received this E-Mail in error, please delete it and advise the sender (by return E-Mail or otherwise) immediately.

This E-Mail (including any attachments) has been scanned for viruses.

It is believed to be free of any virus or other defect that might affect any computer system into which it is received and opened.

However, it is the responsibility of the recipient to ensure that it is virus free.

The sender accepts no responsibility for any loss or damage arising in any way from its use.

E-Mail received by or sent from RBC Capital Markets is subject to review by Supervisory personnel.

Such communications are retained and may be produced to regulatory authorities or others with legal rights to the information.

IRS CIRCULAR 230 NOTICE:  TO COMPLY WITH U.S. TREASURY REGULATIONS, WE ADVISE YOU THAT ANY U.S. FEDERAL TAX ADVICE INCLUDED IN THIS COMMUNICATION IS NOT INTENDED OR WRITTEN TO BE USED, AND CANNOT BE USED, TO AVOID ANY U.S. FEDERAL TAX PENALTIES OR TO PROMOTE, MARKET, OR RECOMMEND TO ANOTHER PARTY ANY TRANSACTION OR MATTER.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20180712/ba2f0745/attachment.html>

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

_______________________________________________
IQUG mailing list
IQUG at iqug.org
http://iqug.org/mailman/listinfo/iqug

End of IQUG Digest, Vol 67, Issue 33
************************************
This email with all information contained herein or attached hereto may contain confidential and/or privileged information intended for the addressee(s) only. If you have received this email in error, please contact the sender and immediately delete this email in its entirety and any attachments thereto.


More information about the IQUG mailing list