Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2007
    Posts
    16

    Unanswered: Suppressing Hyphens in Query Results

    Howdy Folx,

    I am trying to migrate sequences from one schema to another. To do this I wrote a query that would produce a create statement for each existing sequence. That statement fails to produce a syntactically correct sql statement for any sequence for which the next value has never been selected.

    To solve that issue, I wrote a query to produce sql that selects the next value for all existing sequences to ensure there is an entry in SYSIBM.SYSSEQUENCES for each sequence. I execute that query using the CLP syntax:

    Db2 –txf CreateNextValQuery.sql –z nextval.sql

    When I execute CreateNextValQuery.sql I get hyphens (dashes, minus signs…whatever you want to call them…) interleaved in the output. How do I suppress the hyphens? I have tried using translate and replace, but those functions act only on strings returned from the DB and take no action on what I believe to be NULL output. Here is an example of the output followed by both versions of the query that produces it and product information:

    Sample Output:
    -
    SELECT NEXT VALUE FOR D2ENGINE.AUDIT_ADDR_PK_SEQ FROM SCHEMA_A.DUAL;
    SELECT NEXT VALUE FOR D2ENGINE.BLOB_ARCHIVE_SEQ FROM SCHEMA_A.DUAL;
    -
    SELECT NEXT VALUE FOR D2ENGINE.DOC_DISP_PK_SEQ FROM SCHEMA_A.DUAL;
    -
    -

    Query One:
    SELECT
    'SELECT NEXT VALUE FOR ' ||
    (SELECT
    RTRIM(CAST(SEQSCHEMA AS CHAR(30))) ||'.'|| RTRIM(CAST(SEQNAME AS CHAR(30)))
    FROM
    SYSIBM.SYSSEQUENCES SYSSEQ_B
    WHERE
    SYSSEQ_B.SEQNAME=SYSSEQ_A.SEQNAME
    AND
    SYSSEQ_B.SEQSCHEMA=SYSSEQ_A.SEQSCHEMA
    AND
    SYSSEQ_B.LASTASSIGNEDVAL IS NULL
    ) ||
    ' FROM D2ENGINE.DUAL;'
    FROM
    SYSIBM.SYSSEQUENCES SYSSEQ_A
    WHERE
    SYSSEQ_A.SEQSCHEMA in (‘SCHEMA_A’,’SCHEMA_B’,’SCHEMA_C’)
    ;

    Query Two:
    SELECT
    TRANSLATE('SELECT NEXT VALUE FOR ' ||
    (SELECT
    RTRIM(CAST(SEQSCHEMA AS CHAR(30))) ||'.'|| RTRIM(CAST(SEQNAME AS CHAR(30)))
    FROM
    SYSIBM.SYSSEQUENCES SYSSEQ_B
    WHERE
    SYSSEQ_B.SEQNAME=SYSSEQ_A.SEQNAME
    AND
    SYSSEQ_B.SEQSCHEMA=SYSSEQ_A.SEQSCHEMA
    AND
    SYSSEQ_B.LASTASSIGNEDVAL IS NULL
    ) ||
    ' FROM D2ENGINE.DUAL;',';','-')
    FROM
    SYSIBM.SYSSEQUENCES SYSSEQ_A
    WHERE
    SYSSEQ_A.SEQSCHEMA in ('D2ENGINE')
    ;

    Server Info:
    SunOS 5.9 sun4u sparc SUNW,Ultra-Enterprise

    DB2 Version:
    Product Name = "DB2 Enterprise Server Edition"
    Version Information = "8.2"
    Product Name = "DB2 High Availability Disaster Recovery Option"
    Version Information = "8.2"
    Product Name = "DB2 Advanced Security Option"
    Version Information = "8.2"

    DB2 v8.1.0.64
    FixPak 7
    Type ESE

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You could rewrite your queries so that they don't return null values. Failing that, you can always replace null values in the query results with something else by means of the COALESCE function.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2007
    Posts
    16
    coalesce resolved the error...many thanks...
    Last edited by caleysoldman; 10-25-07 at 10:57.

  4. #4
    Join Date
    Jul 2007
    Posts
    16




    Another thanks for hinting that I correctly bind the query...the problem was in my face the whole time...it was my failure to bind the main query that caused the issue...

    SELECT
    COALESCE('SELECT NEXT VALUE FOR ' ||
    (SELECT
    RTRIM(CAST(SEQSCHEMA AS CHAR(30))) ||'.'|| RTRIM(CAST(SEQNAME AS CHAR(30)))
    FROM
    SYSIBM.SYSSEQUENCES SYSSEQ_B
    WHERE
    SYSSEQ_B.SEQNAME=SYSSEQ_A.SEQNAME
    AND
    SYSSEQ_B.SEQSCHEMA=SYSSEQ_A.SEQSCHEMA
    AND
    SYSSEQ_B.LASTASSIGNEDVAL IS NULL
    ) ||
    ' FROM SCHEMA_A.DUAL;',';')
    FROM
    SYSIBM.SYSSEQUENCES SYSSEQ_A
    WHERE
    SYSSEQ_A.SEQSCHEMA in (SCHEMA_A,'SCHEMA_B','SCHEMA_C')
    AND
    SYSSEQ_A.LASTASSIGNEDVAL IS NULL

    ;

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You may want to use the documented catalog views in the SYSCAT schema instead of undocument, internal tables in the SYSIBM schema. In other words: relying on SYSIBM.SYS* may cause you problems in the future because those tables can change at any time.

    Also, you should rethink your approach to mask SQL NULLs . NULL is not the same as an empty string and should not be confused with it.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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