cancel
Showing results for 
Search instead for 
Did you mean: 

SAP IQ 16 sp10.06 after upgrade crashes, stacktrace generated, CURSORS ACTIVE error

Former Member
0 Kudos

Hi all,

I currently have a this problem with my SAP Sybase IQ 16 IQ Version:  SAP IQ/16.0.102.2/20080/P/sp10.06.

Its only crash when I run a bigger query, It doen't happend when I run the same query with little data.

I want to know if you have some idea about this case, 'cause I am looking for this simmilar case but I am not finding answers.

Please if you have some idea I will apreciate it.

I have a lot of memory assigned to SAP IQ 16, in first place It has:

##-iqtc 9830

##-iqmc 6554

Now It has:

-iqmc 16384

-iqtc 14336

The first situation I can appreciate is that the memory in the NMON in my performance tool marks a lot more than the SAP IQ 16 has assigned.

x Top-Processes-(114) qqqqqMode=4  [1=Basic 2=CPU 3=Perf 4=Size 5=I/O 6=Cmds]qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqx

x  PID      %CPU    Size      Res    Res      Res    Char    RAM      Paging        Command                                                                      x

x            Used      KB      Set    Text    Data    I/O    Use  io  other repage                                                                              x

x 5767188  335.5 31799536 31799560      24 31799536  222787  85%      0  13053      0 iqsrv16

This day was 31 GB, but now is 35 GB, this memory is constantly.

Second situation, when I execute a query with large data SAP IQ 16 crash, and send me an stacktrace, always with the same query:

    **  Error from IQ connection:  SA connHandle: 56  SA connID: 10  IQ connID: 0000000243

  User: DBA  

  **  Time of error:  2016-03-23 16:12:41  

  **  IQ Version:  SAP IQ/16.0.102.2/20080/P/sp10.06  

  **  OS info:  IQ built on: RS6000MP/AIX 6.1.0,  Executed on: AIX/bgdcmigiq/1/7/00F79C114C00  

  **  Command status when error occured:  CURSOR ACTIVE  

  **  Command text:

  select "a11"."AR_FNC_ST_TP_ID" as "AR_FNC_ST_TP_ID",

  "max"("a111"."AR_FNC_ST_TP_NM") as "AR_FNC_ST_TP_NM",

  "a15"."GRUPO_ECONOMICO_ID" as "GRUPO_ECONOMICO_ID",

  "max"("a113"."GRUPO_ECONOMICO_NM") as "GRUPO_ECONOMICO_NM",

  "max"("a113"."GRUPO_ECONOMICO_ID_FUENTE") as "GRUPO_ECONOMICO_ID_FUENTE",

  "a14"."IP_ID" as "IP_ID",

  "max"("a15"."EN_ENTE") as "EN_ENTE",

  "max"("a15"."FULL_NM") as "FULL_NM",

  "a11"."AR_ID" as "AR_ID",

  "max"("a19"."NO_DE_OPERACION") as "NO_DE_OPERACION",

  "a11"."AR_LCS_TP_ID" as "AR_LCS_TP_ID",

  "max"("a112"."AR_LCS_TP_NM") as "AR_LCS_TP_NM",

  "a11"."MSR_PRD_ID" as "MSR_PRD_ID",

  "max"("a110"."MES_SHORT") as "MES_SHORT",

  "max"("a110"."MES_DEL_ANO_ID") as "MES_DEL_ANO_ID",

  "a15"."RLN_OFCR_EMPE_ID" as "RLN_OFCR_EMPE_ID",

  "max"("a16"."OFICIAL") as "OFICIAL",

  "max"("a16"."BG_LOGIN") as "BG_LOGIN",

  "count"(distinct "a11"."AR_ID") as "CANTIDADDECONTRATOSVIGENTES",

  "sum"((case when "a11"."BG_SALDO_AJUSTADO" >= 0.0 then("a11"."BG_SALDO_AJUSTADO"-"a11"."BG_VALOR_SUSPENSO") else 0.0 end)) as "SALDOVIGENTES"

  from "DMT_CON"."ANALISIS_DE_CONTRATOS" as "a11"

    join "MSTRDW"."LU_SUB_PRODUCTO" as "a12"

    on("a11"."PD_ID" = "a12"."SUBPRODUCTOBG_ID")

    join "DMT_PAR"."PARTICIPANTE_X_CONTRATO" as "a13"

    on("a11"."AR_ID" = "a13"."AR_ID"

    and "a11"."MSR_PRD_ID" = "a13"."MSR_PRD_ID")

    join "DMT_PAR"."ANALISIS_PARTICIPANTES" as "a14"

    on("a13"."IP_ID" = "a14"."IP_ID"

    and "a13"."MSR_PRD_ID" = "a14"."MSR_PRD_ID")

    join "MSTRDW"."LU_MERCADEO_CLIENTES" as "a15"

    on("a14"."IP_ID" = "a15"."CST_ID")

    join "MSTRDW"."LU_OFICIAL_ACTUAL" as "a16"

    on("a15"."RLN_OFCR_EMPE_ID" = "a16"."OFICIAL_ID")

    join "MSTRDW"."SUBGRUPO_OFICIAL" as "a17"

    on("a16"."SUBGRUPO_OFICIAL_ID" = "a17"."SUBGRUPO_OFICIAL_ID")

    join "DMT_CON"."GRUPO_OFICIAL" as "a18"

    on("a17"."GRUPO_OFICIAL_ID" = "a18"."GRUPO_OFICIAL_ID")

    join "MSTRDW"."VW_MERCADEO_CONTRATOS" as "a19"

    on("a11"."AR_ID" = "a19"."AR_ID")

    join "MSTRDW"."MES" as "a110"

    on("a11"."MSR_PRD_ID" = "a110"."Mes_ID")

    join "MSTRDW"."AR_FNC_ST_TP" as "a111"

    on("a11"."AR_FNC_ST_TP_ID" = "a111"."AR_FNC_ST_TP_ID")

    join "DMT_CON"."AR_LCS_TP" as "a112"

    on("a11"."AR_LCS_TP_ID" = "a112"."AR_LCS_TP_ID")

    join "DMT_CON"."GRUPO_ECONOMICO" as "a113"

    on("a15"."GRUPO_ECONOMICO_ID" = "a113"."GRUPO_ECONOMICO_ID")

  where("a11"."MSR_PRD_ID" = convert(numeric(38),(("datepart"("yy","getdate"())*12)+"datepart"("mm","getdate"())-23165))

  and "a12"."TIPOPRODUCTO_ID" in( 62,60 )

  and "a18"."SECTOR_OFICIAL_ID" in( 174 )

  and "a13"."TIPO_ROL_ID" in( 6738,1955 )

  and "a11"."CANTIDAD_DIAS_MOROSIDAD" >= 1

  and "a11"."AR_LCS_TP_ID" not in( 1123,1166,1119,1171 )

  and "a11"."AR_LCS_TP_ID" not in( 1119,1166,1169,1118,1124,1164,1125,1126,1129,8700 ) )

  group by "a11"."AR_FNC_ST_TP_ID",

  "a15"."GRUPO_ECONOMICO_ID",

  "a14"."IP_ID",

  "a11"."AR_ID",

  "a11"."AR_LCS_TP_ID",

  "a11"."MSR_PRD_ID",

  "a15"."RLN_OFCR_EMPE_ID"

Dump all thread stacks at stcxtlib/st_server.cxx:1921 for PID: 6488080

    ***************** This is the STACKTRACE ***************

===== Thread Number  168595  (IQ connID: 0000000243) =====

pc: 0x900000003eb5948 .pcstkwalk(stk_trace*,int,db_log*,hos_fd*)+0x1c8()

pc: 0x900000003eb5258 .ucstkgentrace(int,int)+0xb8()

pc: 0x900000003eb3a44 .DumpAllThreads(const char*,unsigned int,int)+0x184()

pc: 0x900000003d3e994 .hos_ABORT(const char*,unsigned int,const char*,char*,char*)+0x1b4()

pc: 0x90000000520ba68 .SigHndlr+0x68()

pc: 0x9000000063f19d4 .dfo_Join::SkipRec(dfo*)+0x94()

pc: 0x9000000063f19bc .dfo_Join::SkipRec(dfo*)+0x7c()

pc: 0x900000004d36350 .dfjo_MergeKeyed::FetchOpus()+0x6b0()

pc: 0x9000000063f28d0 .dfo_Join::FetchOpus()+0x30()

pc: 0x900000004a1e664 .dfjo_HashParaT1::FetchOpus()+0x1a4()

pc: 0x9000000063f28d0 .dfo_Join::FetchOpus()+0x30()

pc: 0x900000004a1e664 .dfjo_HashParaT1::FetchOpus()+0x1a4()

pc: 0x9000000063f28d0 .dfo_Join::FetchOpus()+0x30()

pc: 0x900000005099f38 .dfo_cOrderByParaIn::ExecWork(int&,unsigned int)+0xeb8()

pc: 0x900000004d5b324 .workAllocator::DoWork(unsigned int)+0xe4()

pc: 0x900000004d5ef30 .workAllocator::sDoWork(workAllocator*,unsigned int)+0x30()

pc: 0x9000000047ccdc8 .hos_thread::Main()+0x228()

pc: 0x900000003d42384 .hos_lwtask::Start(hos_lwtask*)+0x64()

pc: 0x900000002190c2c .IQWorkerStarter(void*)+0x6c()

pc: 0x900000001f3ca88 .run_task_body+0x48()

pc: 0x900000001f37154 .UnixTask::pre_body(void*)+0x134()

pc: 0x9000000007e0d94 ()

Third situation:

I just upgrade my SAP IQ 16 from a SAP Sybase IQ 15.4

The error is presenting from today, yesterday was done to upgrade SAP SAP IQ / 16.0.102.2 / 20080 / P / sp10.06 from Versn: 15.4.0.3046/141204/P/ESD 5 / RS6000MP / AIX 6.1 .0 / 64bit / 04/12/2014 17:25:56

I ran the sp_iqrebuildindex over all the tables in SAP IQ 16, but the behavior is still occurs.

I Attached some files.

Thanks for the help

Greetings

Santiago

Accepted Solutions (1)

Accepted Solutions (1)

saroj_bagai
Contributor
0 Kudos

Might be related to CR 791237. I would suggest upgrading to SAP IQ 16 sp10.10 and testing your query

Former Member
0 Kudos

Hello:

I will try this in my development enviroment and I will confirm you.

Thanks

Saroj Bagai

Former Member
0 Kudos

Hello Saroj Bagai:

I found a solution, a workaround unless I try the new patch on my development environment.

The solution that prevent this type or error.

We modify this option:

Option Join_Preference= -1

And we are running the query without issues.

About this option:

SAP Sybase IQ 16.0 > Reference: Statements and Options > DatabaseOptions > Alphabetical List of Options

JOIN_PREFERENCE Option

Controls the choice of algorithms when processing joins.

Allowed Values

Value

Action

0

Let the optimizer choose

1

Prefer sort-merge

2

Prefer nested-loop

3

Prefer nested-loop push-down

4

Prefer hash

5

Prefer hash push-down

6

Prefer asymmetric sort-merge join

7

Prefer sort-merge push-down

8

Prefer asymmetric sort-merge push-down join

9

Prefer partitioned hash join if the join keys include all the partition keys of a hash partitioned table

10

Prefer partitioned hash-push down join if the join keys include all the partition keys of a hash partitioned table

11

Prefer partitioned sort-merge join if the join keys include all the partition keys of a hash partitioned table

12

Prefer partitioned sort-merge push-down join if the join keys include all the partition keys of a hash partitioned table

-1

Avoid sort-merge

-2

Avoid nested-loop

-3

Avoid nested-loop push-down

-4

Avoid hash

-5

Avoid hash push-down

-6

Avoid asymmetric sort-merge join

-7

Avoid sort-merge push-down

-8

Avoid asymmetric sort-merge push-down join

-9

Avoid partitioned hash join if the join keys include all the partition keys of a hash partitioned table

-10

Avoid partitioned hash-push down join if the join keys include all the partition keys of a hash partitioned table

-11

Avoid partitioned sort-merge join if the join keys include all the partition keys of a hash partitioned table

-12

Avoid partitioned sort-merge push-down join if the join keys include all the partition keys of a hash partitioned table

Default

0

Scope

Option can be set at the database (PUBLIC) or user level. When set at the database level, the value becomes the default for any new user, but has no impact on existing users. When set at the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.

Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. Takes effect immediately.

Description

For joins within a query, the SAP Sybase IQ optimizer has a choice of several algorithms for processing the join. JOIN_PREFERENCE allows you to override the optimizer’s cost-based decision when choosing the algorithm to use. It does not override internal rules that determine whether an algorithm is legal within the query engine. If you set it to any nonzero value, every join in a query is affected; you cannot use it to selectively modify one join out of several in a query, but join condition hint strings can do so.

This option is normally used for internal testing or tuning of report queries, and only experienced DBAs should use it.

Simple equality join predicates can be tagged with a predicate hint that allows a join preference to be specified for just that one join. If the same join has more than one join condition with a local join preference, and if those hints are not the same value, then all local preferences are ignored for that join. Local join preferences do not affect the join order chosen by the optimizer.

This example requests a hash join:

AND (T.X = 10 * R.x, 'J:4')

Now we are running the query and do not have any issues.

Thanks for the help

Greetings

Santiago Jacome

Former Member
0 Kudos

Hellow Saroj Bagai:

I am trying to find this CR 791237, but I can not reach nothing.

Please could you send me a link or another information about this CR.

I will apreciate your help a lot.

After I apply the next workarounds:

set temporary option AGGREGATION_PREFERENCE='-2' /* Avoid aggregation

using IQ indexes */

set temporary option  JOIN_PREFERENCE='-1'

My SAP IQ 16 hang up and It became unstable.

These workarounds caused that SAP IQ hang up without any messages in iqmsg, and the performance in the queries was very slow.

Thanks

Santiago Jacome

Answers (0)