DB2 graphic  QMF Version 8

Data types you can use with DBCS data

You can save DBCS data in your database if you define the columns in which you save the data as character or graphic. Whether you save your DBCS data in character or graphic columns depends on your needs:

Specifically, QMF can save DBCS data in database columns that are defined as these data types:

Character
DBCS data, when preceded and followed by single-byte single quotation marks, can appear in columns with a character data type. QMF also allows DBCS data strings that are mixed with SBCS data strings. Use this data type if all entries in the column have the same length, up to a maximum of 126 double-byte characters.
Graphic
QMF can put only DBCS data of fixed length into columns that are defined as graphic data type. Use this data type if all entries in the column have the same length, up to a maximum of 127 double-byte characters.
Variable character
Use this for variable-length entries of up to 126 double-byte characters. DBCS data, when preceded and followed by single-byte, single quotation marks, can appear in columns with a variable data type. QMF also allows DBCS data strings that are mixed with SBCS data strings.

In DB2, variable character data can exceed 126 characters. When variable character data exceeds 126 characters, it is handled like the LONG VARCHAR data type.

Variable graphic
QMF can put only DBCS data of variable length up to 127 characters into a column that is defined as VARGRAPHIC data type.
Long variable character
Use this data type with caution. LONG VARCHAR can be up to 16,382 double-byte characters long. QMF has restrictions for how you can use a column with LONG VARCHAR in a query. You cannot use it:
Long variable graphic
QMF can put only DBCS data of variable length up to 16,383 characters into a column that is defined as LONG VARGRAPHIC data type. Use this data type with caution. The restrictions for how you can use a column with this data type in a query are the same as for the long variable character data type.

LOB data types

In DB2 QMF Version 8.1, LOB data types CLOB, DBCLOB, and BLOB can now be displayed in a QMF report without having to be casted as VARCHAR or VARGRAPHIC data types. The size of a LOB data row ranges from 0 bytes to 2 GB minus one byte.

LOB Descriptor Area (LOB DA)

Because of the potential size of LOB data, users may want to limit the viewing of actual data within a report. Instead of displaying the actual LOB data by default, the LOB DA, consisting of the LOB data type name and defined length of the LOB data, will be displayed. The LOB DA is specified through the setting of edit code 'M' (new in QMF Version 8.1) on every LOB column by default. See Specifying punctuation for the values in a column

Greater than 32 KB support for LOB data type columns

The following EXEC SQL statements are necessary in order to determine the potential number of host variables required to deal with large LOB (greater than 32 KB up to 2 GB).

The result of these statements will be a one-column result table with each row containing a 4 byte length. Each length will be set into the field LOB_DLEN for the particular LOB record.

A minimimum of five data definition language (DDL) SQL queries must be issued for each request to create a LOB table. For n number of LOB columns, 2 + 3n CREATE statement queries are required. LOB tables will not function if any part of the definition is missing. Here are five required steps to create a complete definition for a LOB table:

  1. Create a base LOB table
    CREATE TABLE LOB (COLCHAR(8), CLOB CLOB(4K), BLOB BLOB(4K),
    ID ROWID NOT NULL GENERATED BY DEFAULT) IN DSQDBDEF.DSQTSDEF
    Tables with LOB columns must also have a ROWID column. The LOB length can be up to 2 GB.
  2. Create an index on the LOB table
    CREATE TYPE 2 UNIQUE INDEX MCOATES.LOBID ON MCOATES.LOB(ID)
  3. Create tablespace for each LOB column
    CREATE LOB TABLESPACE LOBTB IN DSQDBDEF LOCKSIZE LOB USING STOGROUP
     DSQSGDEF PRIQTY 1 SECQTY 0 BUFFERPOOL BP0 CLOSE NO
    CREATE LOB TABLESPACE LOBTB2 IN DSQDBDEF LOCKSIZE LOB USING STOGROUP
     DSQSGDEF PRIQTY 1 SECQTY 0 BUFFERPOOL BP0 CLOSE NO
  4. Create an auxiliary table for each LOB column
    CREATE AUX TABLE MCOATES.AXCLOB IN DSQDBDEF.LOBTB STORES
    MCOATES.LOB
    COLUMN CLOB
    CREATE AUX TABLE MCOATES.AXBLOB IN DSQDBDEF.LOBTB2 STORES
    MCOATES.LOB
    COLUMN BLOB
  5. Create an index for the auxiliary table
    CREATE INDEX MCOATES.AXCLOBX ON MCOATES.AXCLOB
    CREATE INDEX MCOATES.AXBLOBX ON MCOATES.AXBLOB
    Table-name, database-name, and table-space-name are required host variable parameters.


Go to the previous page Go to the next page

Downloads | Library | Support | Support Policy | Terms of use | Feedback
Copyright IBM Corporation 1982,2004 Copyright IBM Corporation 1982, 2004
timestamp Last updated: March, 2004