Topic
7 replies Latest Post - ‏2012-05-31T20:23:06Z by krmilligan
ZarykJag
ZarykJag
6 Posts
ACCEPTED ANSWER

Pinned topic as400 db2 7R1 - SQL Views

‏2012-05-24T11:46:19Z |
as400 db2 7R1 - SQL Stored Procedures

We have run into an issue with calling views from a stored procedure.

When the view is called, another thread is being created, so there are two threads running under the one job.



It’s been running for about 15 minutes.

QZDASOINIT USER PJ 36.6 RUN

Total Aux Run
Opt Thread Name Status CPU I/O Priority
00000278 TIMA 139.663 33172 20
0000027E RUN 673.188 5 20

CALL STACK for 278: Not Sure what this is

Type Program Statement Procedure
QZDASOINIT QSYS _CXX_PEP__Fv
QZDASOINIT QSYS 4 main
QZDASRV QSYS 10925 QZDACMDP
QZDASRV QSYS 24358 SQL_CODE
QZDASRV QSYS 25272 SQL_EXTPGM
QZDASRV QSYS 24545 SQL_ECALL
QZDASRV QSYS 17738 QZDASQL
QZDASRV QSYS 23804 FETCH
QZDASRV QSYS 29865 RSFETCH
QSQROUTX QSYS 11255 QSQROUTX
QSQROUTX QSYS 24228 XTPROCES
QSQROUTX QSYS 25616 XTDCALLS
QSQRUN2 QSYS 11509 SQL_Fetch
QSQRUN2 QSYS 12116 F_BLKRCDS
QSQRUN2 QSYS 13778 F_GETNEXTL
QSQRUN2 QSYS 19554 F_GETBLK
QDBGETMQO QSYS 2899 QDBGETMQO

Updated on 2012-05-31T20:23:06Z at 2012-05-31T20:23:06Z by krmilligan
  • ZarykJag
    ZarykJag
    6 Posts
    ACCEPTED ANSWER

    Re: as400 db2 7R1 - SQL Views

    ‏2012-05-24T11:50:44Z  in response to ZarykJag
    sorry accidently hit tab, and space, and posted the question.

    So to continue........
    When we use the view, it create job locks that look like Figure 1, but if we take the select statement from the view and replace the view name with the select, it looks like figure 2.

    Can anyone help out with this?
    Figure 1:


    Old Job Locks screen:
    Object Member ASP
    Opt Object Library Type Lock Status Locks Device
    APPS_IDS QSYS *USRPRF *SHRRD HELD
    CASES QTEMP *FILE-PHY *SHRRD HELD YES
    *SHRRD HELD
    CMCPCH0F PCMSPD *FILE-LGL *SHRRD HELD YES
    ITCOURT QSYS *USRPRF *SHRRD HELD
    PTPROSF PCMSPD *FILE-LGL *SHRRD HELD YES
    QASQRESL QSYS2 *FILE-LGL *SHRRD HELD YES
    QSQPTABL QSYS2 *FILE-PHY *SHRRD HELD YES
    *SHRRD HELD
    QZDAPKG QGPL *SQLPKG *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    SCJDREAD QSYS *USRPRF *SHRRD HELD
    SCOBJOWN QSYS *USRPRF *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    SPARTAN QSYS *USRPRF *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    SYSROUTINE QSYS2 *FILE-PHY *SHRRD HELD YES
    VWWB_00001 PCMSPD *FILE-LGL *SHRRD HELD YES

    Figure 2:


    New Job Locks Screen:
    Object Member ASP
    Opt Object Library Type Lock Status Locks Device
    QIWS QSYS *LIB *SHRRD HELD
    QSQPTABL QSYS2 *FILE-PHY *SHRRD HELD YES
    QSYS QSYS *LIB *SHRRD HELD
    QSYS2 QSYS *LIB *SHRRD HELD
    QTEMP QSYS *LIB *SHRRD HELD
    QUSRSYS QSYS *LIB *SHRRD HELD
    QZDAPKG QGPL *SQLPKG *SHRRD HELD
    *SHRRD HELD
    SPARTAN QSYS *USRPRF *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    SYSROUTINE QSYS2 *FILE-PHY *SHRRD HELD YES
  • ZarykJag
    ZarykJag
    6 Posts
    ACCEPTED ANSWER

    Re: as400 db2 7R1 - SQL Views

    ‏2012-05-24T11:56:47Z  in response to ZarykJag
    This is the second thread call stack,

    CALL STACK FOR 27E: Changes multiple times as if going through the same process multiple times. This looks to be where it is calling the search stored procedure, and just going through inner joining the data based on the conditions.

    Type Program Statement Procedure
    QLESPI QSYS 19 LE_Create_Thread2__FP12crtt >
    QQQOOOUPCL QSYS 5 DbopThreadMain__FP14DbopThr >
    QQQOOOUPCL QSYS 2959 DBOPUPCALLUDF
    QQQSVRTN QSYS 11252 QQINVOKEUDF
    QQQSVUSR QSYS 2037 QQINVOKEUSER
    QQQSVUSR QSYS 2533 CALLUDFPROCEDURE
    FNWB_00002 PCMSPD 65 FNWB_FORMATNAME_1
    QSQROUTE QSYS 11321 QSQROUTE
    QSQROUTE QSYS 19556 FASTPATH_PROC
    QSQRUN2 QSYS 14091 SQL_ValuesInto
    QSQRUN2 QSYS 14502 F_ASSIGNL
    QDBGETMQO QSYS 2899 QDBGETMQO
  • ZarykJag
    ZarykJag
    6 Posts
    ACCEPTED ANSWER

    Re: as400 db2 7R1 - SQL Views

    ‏2012-05-25T11:59:08Z  in response to ZarykJag
    Is there any new features within 7r1 that creates a new thread for functions being called or being called specifically from views? It looks like the function is causing the second thread and using up more cpu% than necessary. I can replace the function call with the contents of the function, and the thread goes away.
    • krmilligan
      krmilligan
      433 Posts
      ACCEPTED ANSWER

      Re: as400 db2 7R1 - SQL Views

      ‏2012-05-29T15:57:30Z  in response to ZarykJag
      Function invocations can run in a separate thread. Have you tried creating your function as NOT FENCED?
    • B.Hauser
      B.Hauser
      241 Posts
      ACCEPTED ANSWER

      Re: as400 db2 7R1 - SQL Views

      ‏2012-05-30T06:23:51Z  in response to ZarykJag
      >> using up more cpu% than necessary.

      What's your main concern? The view being executed in a separate thread or using more CPU% than "necessary"?

      Could it be a temporary index must be built when accessing the view? Index generation will always take as much CPU% as it can get.

      Birgitta
  • zaryk
    zaryk
    1 Post
    ACCEPTED ANSWER

    Re: as400 db2 7R1 - SQL Views

    ‏2012-05-30T15:41:18Z  in response to ZarykJag
    Actually, both. The function was creating the second thread, and causing more CPU% than necessary. "More CPU% as necessary" meaning most of the other apps are running at 0%-6%. When this stored procedure was called, it would run 40%-70%. I replaced the function of the content and the thread and cup% look normal.

    Access plans are turned off, so all jobs use the same name QZDASOINIT.

    Either the view, dynamic sql, "SET OPTION Commit=*None, SRTSEQ=*HEX, DynUsrPrf=*OWNER, DlyPrp=*YES" or:

     
    fetch first 25 rows only
    for read only optimize for 25 rows;

    causes this to occur, and when this occurs "Access Plan not updated" messages occur; and a second job may be opened to compensate for this the job locks occurring.

     
    Object Member ASP
    Opt Object Library Type Lock Status Locks Device
    APPS_IDS QSYS *USRPRF *SHRRD HELD
    CASES QTEMP *FILE-PHY *SHRRD HELD YES
    *SHRRD HELD
    CMCPCH0F PCMSPD *FILE-LGL *SHRRD HELD YES
    ITCOURT QSYS *USRPRF *SHRRD HELD
    PTPROSF PCMSPD *FILE-LGL *SHRRD HELD YES
    QASQRESL QSYS2 *FILE-LGL *SHRRD HELD YES
    QSQPTABL QSYS2 *FILE-PHY *SHRRD HELD YES
    *SHRRD HELD
    QZDAPKG QGPL *SQLPKG *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    SCJDREAD QSYS *USRPRF *SHRRD HELD
    SCOBJOWN QSYS *USRPRF *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    SPARTAN QSYS *USRPRF *SHRRD HELD
    *SHRRD HELD
    *SHRRD HELD
    SYSROUTINE QSYS2 *FILE-PHY *SHRRD HELD YES
    VWWB_00001 PCMSPD *FILE-LGL *SHRRD HELD YES
    I am not a guru, so more testing needs to be done, but the extra thread, and cpu% seem to have been fixed. And no I haven't tried "Not Fenced" for the function.
    • krmilligan
      krmilligan
      433 Posts
      ACCEPTED ANSWER

      Re: as400 db2 7R1 - SQL Views

      ‏2012-05-31T20:23:06Z  in response to zaryk
      For performance reasons, it's recommended that you use the NOT FENCED attributes for function definitions.