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

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.

coalesce resolved the error...many thanks...

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
;

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.