ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

View with Current Date

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • View with Current Date

    I have a requirement where I shall have to create a view to pull last 2 years worth of data from a table which has lots of history. Here is my view definition.

    CREATE VIEW View1 AS SELECT * FROM TAB1 WHERE UDATE >= CAST((YEAR(CURRENT DATE) - 2) || '0000' AS DEC(8,0))


    The view is created without any issues and I can run the Select against the view and I'm seeing the results.

    However, something weird going on when I use the above view in an SQL program to join against another table. The Select statement has Distinct and Order By clause as well.

    When I run the program it gets stuck on the Fetch statement forever. The job doesn't makes any progress but when I see the Threads (Option 20) from Work Job there is a thread which is constantly spinning the Total CPU and Aux I/O.

    Any idea what might be going in the background.

  • #2
    Can you share the definition of table TAB1, the definition of the table you are joining with, and the join SQL?

    Comment


    • #3
      I noticed something strange.

      I'm running the same process in different systems. In one of the system it just works fine however on the other it won't. What I noticed was the program is getting stuck on my main SQL query.

      Main SQL Query:
      Select distinct
      CUNO,INVN,IBDT,IAMT,
      SLOC,NAME,NAM2,STNO,
      STRT,CITY,STCD,ZIPC,
      PONO,POEF,POEN,
      CINV,STAT,UPGM,USER,
      UDAT,UTIM,CRTD,CRTT
      from VIEW1 join TAB2 on CINV = INV#
      where COMP = ' A01' and UDAT >= '20150101'
      order by NANO, CINV

      VIEW1 -> CREATE VIEW View1 AS SELECT * FROM TAB1 WHERE UDATE >= CAST((YEAR(CURRENT DATE) - 2) || '0000' AS DEC(8,0))


      What I noticed was on the system it is working fine behind the scenes the process is using LOGICAL1 build on TAB1. However, on the system where it is not working the process behind the scenes is using LOGICAL2 on TAB1.

      LOGICAL 1: Keyed on UDAT
      LOGICAL 2: Keyed on CINV, UDAT

      What makes the system use one logical versus the other behind the scenes.

      Any inputs.

      Comment


      • #4
        The main query and the logical reference field name "UDAT", but the view references "UDATE"? Is that correct?

        Comment


        • #5
          "What makes the system use one logical versus the other behind the scenes."

          The select statement - if you're going to do an ORDER BY CIINV or do a comparison against CINV it might pick Logical 2 over logical 1 for example. It'll pick the index that makes the most sense for performance - it shouldn't effect what data you get, just performance behind the scenes.

          Comment

          Working...
          X