[IQUG] [CAUTION] Re: [CAUTION] Re: SQLtrace

Mumy, Mark mark.mumy at sap.com
Thu Jul 12 06:36:24 MST 2018


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/d3f15fba/attachment-0001.html>


More information about the IQUG mailing list