Results 1 to 2 of 2

Thread: Pleas Help

  1. #1
    Join Date
    Aug 2003
    Location
    Allentown pa
    Posts
    41

    Unanswered: Pleas Help

    Hi,

    We just loaded Informix 9.40 fc2. All is working well except one report that runs through power builder. It seems the stored procedure syntax is not right as you can see. The user sleeps forever. Below is the user that was running the report session info. Is there something we need to do with the stored procedures? We migrated from 7.30.

    Thank you so much in advance
    Jeff


    IBM Informix Dynamic Server Version 9.40.FC2 -- On-Line -- Up 4 days 09:34:3
    4 -- 3563520 Kbytes

    session #RSAM total used dyna
    mic
    id user tty pid hostname threads memory memory expl
    ain
    97099 sgrace PHILA-3F 408 unknown- 1 229376 217416 off

    tid name rstcb flags curstk status
    95292 sqlexec 2224bc178 ---PR-- 11288 sleeping(Forever)

    Memory pools count 2
    name class addr totalsize freesize #allocfrag #freefrag
    97099 V 22422d040 163840 10104 261 5
    97099_SORT_ V 2299cb040 65536 1856 339 1

    name free used name free used
    overhead 0 6496 scb 0 144
    opentable 0 7832 filetable 0 1920
    ru 0 280 misc 0 144
    log 0 2184 temprec 0 16248
    keys 0 592 ralloc 0 77200
    gentcb 0 1680 ostcb 0 3440
    sort 0 60552 sqscb 0 21696
    sql 0 72 srtmembuf 0 128
    rdahead 0 224 hashfiletab 0 552
    osenv 0 632 buft_buffer 0 2160
    sqtcb 0 5200 fragman 0 7480
    shmblklist 0 416 sapi 0 64

    sqscb info
    scb sqscb optofc pdqpriority sqlstats optcompind dire
    ctives
    22aa41050 22723d028 0 0 0 0 1


    Sess SQL Current Iso Lock SQL ISAM F.E.
    Id Stmt type Database Lvl Mode ERR ERR Vers Explain

    97099 SELECT custdb DR Not Wait 0 0 7.20 Off


    Current statement name : __12_cursor1
    Stored procedure stack :

    context proc-counter opcode name
    ------------------------------------------------------------------
    0x00000002264fb050 0x2325bace8+0x0080 CURSOR custdb:ar_accrual_rpt_new
    0x00000002264fb050 0x2325bace8+0x0080 CURSOR custdb:ar_accrual_rpt_new

    Current SQL statement in procedure custdb:ar_accrual_rpt_new
    proc-counter 0x2325bace8 opcode CURSOR

    "12$cursor1" is select unique custdef.account, custdef.name1, custdef.name2, custdef.repnum, custdef.canceldate, reps.rep_sales
    from arbase, custdef, outer(reps) where (and (and (and (= arbase.account, custdef.account), (= custdef.repnum, r
    eps.rep_num)), (<> arbase.amount, 0)), (<> arbase.ttype, 101))

    Last parsed SQL statement :
    execute procedure ar_accrual_rpt_new ( s_atx = 'Y', s_atx_east = 'Y',
    s_midwest = 'Y',d_start = DATETIME (2003-11-01) YEAR TO DAY, d_end =
    DATETIME (2003-11-30) YEAR TO DAY);
    Jeff Wain

  2. #2
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    I experienced a similar situation when migrating from IDS 7.31.UC3 to 9.30.UC6 .... performance was dreadful .... but this may not address the root cause of your problem but it won't hurt to try.

    At the suggestion of a knowledgeable friend I did the following in dbaccess to each DB:

    update statistics drop distributions;

    update statistics medium distributions only;

    Outside of dbaccess, run whatever script you would normally run to update statistics on the entire DB.

    This restored peformance back to acceptable levels

    Good luck, hope this works for you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •