Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    17

    Unanswered: Select Statement Query

    Hi All,

    I am newbie in scripting SQL. Can any one assist.

    1. How to loop the returned values from a select statement.

    2. I need to compare the values returned from this select row by row.

    3.I can see few examples for Locate function in DB2, however
    how to use select in the locate function.

    I need to combine the below two statement (Select and locate)

    "SELECT SUBSTR(TBSP_NAME, 1,20) AS TBSP_NAME, SUBSTR(CONTAINER_NAME, 1,70) AS CONTAINER_NAME FROM SYSIBMADM.SNAPCONTAINER "

    OUTPUT

    TBSP_NAME CONTAINER_NAME
    -------------------- -----------------------------------------------------
    SYSCATSPACE /FIN_PAT/SYSCATALOG
    TEMPSPACE1 /FIN_PAT/TEMPSPACE1
    USERSPACE1 /FIN_PAT/USERSPACE1
    SYSTOOLSPACE /FIN_PAT/SYSTOOLSPACE
    SYSTOOLSTMPSPACE /FIN_PAT/SYSTOOLSTMPSPACE
    FINSYSTMP32K /FIN_PAT/FINSYSTMP32K
    TSCD /FIN_PAT/TSCD
    USERSPACE_32K /FIN_PAT/USERSPACE_32K
    TSCD32K /FIN_PAT/TSCD32K
    TEMP32K /FIN_PAT/TEMP32K
    TSCONTROL /FIN_PAT/TSCONTROL
    TSDELTA /FIN_PAT/T0000011/C0000000.LRG

    I need to compare the above output row by row, for example.

    All I am trying to do is match TBSP_NAME against container name (after removing the /FIN_PAT/

    For example.

    SELECT LOCATE('SYSCATSPACE', /FIN_PAT/SYSCATALOG')
    FROM SYSIBM.SYSDUMMY1

    Thanks in advance

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by iam_hari View Post
    Hi All,

    I am newbie in scripting SQL. Can any one assist.

    1. How to loop the returned values from a select statement.

    2. I need to compare the values returned from this select row by row.

    3.I can see few examples for Locate function in DB2, however
    how to use select in the locate function.

    I need to combine the below two statement (Select and locate)

    "SELECT SUBSTR(TBSP_NAME, 1,20) AS TBSP_NAME, SUBSTR(CONTAINER_NAME, 1,70) AS CONTAINER_NAME FROM SYSIBMADM.SNAPCONTAINER "

    OUTPUT

    TBSP_NAME CONTAINER_NAME
    -------------------- -----------------------------------------------------
    SYSCATSPACE /FIN_PAT/SYSCATALOG
    TEMPSPACE1 /FIN_PAT/TEMPSPACE1
    USERSPACE1 /FIN_PAT/USERSPACE1
    SYSTOOLSPACE /FIN_PAT/SYSTOOLSPACE
    SYSTOOLSTMPSPACE /FIN_PAT/SYSTOOLSTMPSPACE
    FINSYSTMP32K /FIN_PAT/FINSYSTMP32K
    TSCD /FIN_PAT/TSCD
    USERSPACE_32K /FIN_PAT/USERSPACE_32K
    TSCD32K /FIN_PAT/TSCD32K
    TEMP32K /FIN_PAT/TEMP32K
    TSCONTROL /FIN_PAT/TSCONTROL
    TSDELTA /FIN_PAT/T0000011/C0000000.LRG

    I need to compare the above output row by row, for example.

    All I am trying to do is match TBSP_NAME against container name (after removing the /FIN_PAT/

    For example.

    SELECT LOCATE('SYSCATSPACE', /FIN_PAT/SYSCATALOG')
    FROM SYSIBM.SYSDUMMY1

    Thanks in advance
    Is this what you are looking for?

    Code:
    with t(s) as (
        values 'SYSCATSPACE /FIN_PAT/SYSCATALOG'
            ,  'TEMPSPACE1 /FIN_PAT/TEMPSPACE1'
            ,  'USERSPACE1 /FIN_PAT/USERSPACE1'
            ,  'SYSTOOLSPACE /FIN_PAT/SYSTOOLSPACE'
            ,  'SYSTOOLSTMPSPACE /FIN_PAT/SYSTOOLSTMPSPACE'
            ,  'FINSYSTMP32K /FIN_PAT/FINSYSTMP32K'
            ,  'TSCD /FIN_PAT/TSCD'
            ,  'USERSPACE_32K /FIN_PAT/USERSPACE_32K'
            ,  'TSCD32K /FIN_PAT/TSCD32K'
            ,  'TEMP32K /FIN_PAT/TEMP32K'
            ,  'TSCONTROL /FIN_PAT/TSCONTROL'
            ,  'TSDELTA /FIN_PAT/T0000011/C0000000.LRG'
    )
    select tbsp_name from (
        select substr(s,1,locate(' ',s)) as tbsp_name
            ,  substr(substr(s,locate(' ',s)),length('/FIN_PAT/')+2)
            as container_name
        from t
    )
    where tbsp_name = container_name;
    Last edited by lelle12; 11-14-12 at 08:27.
    --
    Lennart

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    iam_hari, here is one way it could be done using the Locate function. You just need to put it in the WHERE clause where the comparison is made:
    Code:
    WITH TEST_TAB (TBSP_NAME, CONTAINER_NAME)
      AS (
          SELECT 'SYSCATSPACE'     , '/FIN_PAT/SYSCATALOG'            FROM SYSIBM.SYSDUMMY1 UNION ALL  
          SELECT 'TEMPSPACE1'      , '/FIN_PAT/TEMPSPACE1'            FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'USERSPACE1'      , '/FIN_PAT/USERSPACE1'            FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'SYSTOOLSPACE'    , '/FIN_PAT/SYSTOOLSPACE'          FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'SYSTOOLSTMPSPACE', '/FIN_PAT/SYSTOOLSTMPSPACE'      FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'FINSYSTMP32K'    , '/FIN_PAT/FINSYSTMP32K'          FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'TSCD'            , '/FIN_PAT/TSCD'                  FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'USERSPACE_32K'   , '/FIN_PAT/USERSPACE_32K'         FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'TSCD32K'         , '/FIN_PAT/TSCD32K'               FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'TEMP32K'         , '/FIN_PAT/TEMP32K'               FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'TSCONTROL'       , '/FIN_PAT/TSCONTROL'             FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'TSDELTA'         , '/FIN_PAT/T0000011/C0000000.LRG' FROM SYSIBM.SYSDUMMY1
         )
    SELECT *
    FROM TEST_TAB
    WHERE LOCATE(TBSP_NAME, CONTAINER_NAME) > 0
    
    TBSP_NAME        CONTAINER_NAME                
    ---------------- ------------------------------
    TSCONTROL        /FIN_PAT/TSCONTROL            
    TEMP32K          /FIN_PAT/TEMP32K              
    TSCD32K          /FIN_PAT/TSCD32K              
    USERSPACE_32K    /FIN_PAT/USERSPACE_32K        
    TSCD             /FIN_PAT/TSCD                 
    FINSYSTMP32K     /FIN_PAT/FINSYSTMP32K         
    SYSTOOLSTMPSPACE /FIN_PAT/SYSTOOLSTMPSPACE     
    SYSTOOLSPACE     /FIN_PAT/SYSTOOLSPACE         
    USERSPACE1       /FIN_PAT/USERSPACE1           
    TEMPSPACE1       /FIN_PAT/TEMPSPACE1           
    
      10 record(s) selected.
    Depending on your needs, this may give false information. For example the last row in the Common Table Expression I used to create a working table was:

    'TSDELTA' , '/FIN_PAT/T0000011/TSDELTA'

    The row would have been returned even though it had more than just /FIN_PAT/ in front of it. If this is what you want (TBSP_NAME is anywhere in CONTAINER_NAME regardless of what ever else might be there) then this will work. If you only want rows that had ONLY /FIN_PAT/ before the name, it won't.

    This method will work, if the later is the case (there are probably other methods):
    Code:
    SELECT *
    FROM TEST_TAB
    WHERE '/FIN_PAT/' CONCAT TBSP_NAME = CONTAINER_NAME
    Instead of removing the first part of CONTAINER_NAME, you can just concatenate it to the first part of the TBSP_NAME in the Where clause where the comparison is made.

    PS You need to put in the DB2 type, Version, and Fix Pack level when posting as some solutions require certain levels to work.

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow The query

    In another words the query could be like following:

    Code:
    SELECT 
    SUBSTR(TBSP_NAME, 1,20) AS TBSP_NAME, 
    SUBSTR(CONTAINER_NAME, 1,70) AS CONTAINER_NAME 
    FROM SYSIBMADM.SNAPCONTAINER 
    where 
    Locate(strip(SUBSTR(TBSP_NAME, 1,20)), SUBSTR(CONTAINER_NAME, 1,70)) 
    > 
    Locate('/FIN_PAT/', SUBSTR(strip(CONTAINER_NAME), 1,70)) 
    order by 1, 2;
    Lenny

Posting Permissions

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