Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    18

    Unanswered: Performance Problem DB2-LUW V9 using nicknames

    Hi,

    i´ve got some strange Problem. I defined a nickname within a DB2-LUW-V9-DB which points to a table in a DB2-Z/OS-V8-Subsystem.

    I did a select with "fetch first n Rows". The complete result-set is 1200 rows.
    When I specify "fetch first 900 rows" the result is given immediately.
    When I specify "fetch first 1000 rows" the result is given after approx. 20 secs.

    The same selects native on the host-DBs show all results immediately.
    The access-path of both select-stmts is identical.

    To me it looks like a caching-Problem or smthg. I suppose some db- or dbm-cfg things to do.

    Any Ideas ?

    DB2-Z/OS-V8 and DB2-LUW on Suse/Linux/SLES9 with
    Informational tokens are "DB2 v9.1.0.4", "s071028", "MI00215", and Fix Pack
    "4".

    Thx in advance, Willi

  2. #2
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Can you try the following as well and see whether you see any different behavior:

    OPTIMIZE FOR 1000 rows FOR FETCH ONLY with ur

    For making Federated table-access faster, there may be hardly any EXTRA parameter which needs to be taken care of. If you are getting 900 records faster, then why not 100 records more.

  3. #3
    Join Date
    Aug 2004
    Posts
    18
    hi, i tried it out, no change...

    select current_timestamp from sysibm.sysdummy1

    1
    --------------------------
    2008-11-19-12.53.52.222576

    1 record(s) selected.


    SELECT A.SEX120, A.SEX020, A.SEX040, B.SIN100, A.SEX160, ...
    FROM gporsexe AS A
    INNER JOIN gporsins AS B ON (a.GPORSEXE_KEY1 = b.gporsins_key1) WHERE (a.SEX100 ='11.11.2008')
    OPTIMIZE FOR 900 rows FOR FETCH ONLY with ur;

    1202 record(s) selected.


    select current_timestamp from sysibm.sysdummy1

    1
    --------------------------
    2008-11-19-12.53.52.445559

    --------------------------------------------------------------------------

    select current_timestamp from sysibm.sysdummy1

    1
    --------------------------
    2008-11-19-13.00.03.887055

    1 record(s) selected.

    SELECT A.SEX120, A.SEX020, A.SEX040, B.SIN100, A.SEX160, ...
    FROM gporsexe AS A
    INNER JOIN gporsins AS B ON (a.GPORSEXE_KEY1 = b.gporsins_key1) WHERE (a.SEX100 ='11.11.2008')
    OPTIMIZE FOR 1000 rows FOR FETCH ONLY with ur;

    1202 record(s) selected.


    select current_timestamp from sysibm.sysdummy1

    1
    --------------------------
    2008-11-19-13.02.27.368829

    1 record(s) selected.

    --------------------------------------------------------------------------

    Will try to catch thread-information using our Z/OS-Monitor....

  4. #4
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Are you running this from some third-party tool (like: RapidSql/DbArtisan) etc? Over there, the result-sets are shown in batches (like first 500, then next 500) based on your setting.

  5. #5
    Join Date
    Aug 2004
    Posts
    18
    no, i did it from db2p ( command line )

  6. #6
    Join Date
    Aug 2004
    Posts
    18
    I got it. I explained the Stmt on the Z/OS-Side and found no differences. I assumed the stmt being ship completely to Z/OS.
    An explain on DB2-LUW showed different access Paths. With "optimize/fetch for 900 Rows" the stmt was shipped completely. With "optimize/fetch for 1000 Rows" the optimizer decided to read both tables seperately and perform the hash-join on the DB2-LUW Side.

Posting Permissions

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