public DB2Dictionary() 
{
      
        platform = "DB2";
        validationSQL = "SELECT DISTINCT(CURRENT TIMESTAMP) FROM "
            + "SYSIBM.SYSTABLES";
        supportsSelectEndIndex = true;
        nextSequenceQuery = "VALUES NEXTVAL FOR {0}";
        sequenceSQL = "SELECT SEQSCHEMA AS SEQUENCE_SCHEMA, "
            + "SEQNAME AS SEQUENCE_NAME FROM SYSCAT.SEQUENCES";
        sequenceSchemaSQL = "SEQSCHEMA = ?";
        sequenceNameSQL = "SEQNAME = ?";
        characterColumnSize = 254;
        binaryTypeName = "BLOB(1M)";
        longVarbinaryTypeName = "BLOB(1M)";
        varbinaryTypeName = "BLOB(1M)";
        clobTypeName = "CLOB(1M)";
        longVarcharTypeName = "LONG VARCHAR";
        datePrecision = MICRO;
        storeCharsAsNumbers = false;
        fixedSizeTypeNameSet.addAll(Arrays.asList(new String[]{
            "LONG VARCHAR FOR BIT DATA", "LONG VARCHAR", "LONG VARGRAPHIC",
        }));
        systemSchemas = new String(
                "SYSCAT,SYSIBM,SYSSTAT,SYSIBMADM,SYSTOOLS");
        maxConstraintNameLength = 18;
        maxIndexNameLength = 18;
        maxColumnNameLength = 30;
        supportsDeferredConstraints = false;
        supportsDefaultDeleteAction = false;
        supportsAlterTableWithDropColumn = false;
        supportsNullTableForGetColumns = false;
        requiresCastForMathFunctions = true;
        requiresCastForComparisons = true;
        reservedWordSet.addAll(Arrays.asList(new String[]{
            "AFTER", "ALIAS", "ALLOW", "APPLICATION", "ASSOCIATE", "ASUTIME",
            "AUDIT", "AUX", "AUXILIARY", "BEFORE", "BINARY", "BUFFERPOOL",
            "CACHE", "CALL", "CALLED", "CAPTURE", "CARDINALITY", "CCSID",
            "CLUSTER", "COLLECTION", "COLLID", "COMMENT", "CONCAT",
            "CONDITION", "CONTAINS", "COUNT_BIG", "CURRENT_LC_CTYPE",
            "CURRENT_PATH", "CURRENT_SERVER", "CURRENT_TIMEZONE", "CYCLE",
            "DATA", "DATABASE", "DAYS", "DB2GENERAL", "DB2GENRL", "DB2SQL",
            "DBINFO", "DEFAULTS", "DEFINITION", "DETERMINISTIC", "DISALLOW",
            "DO", "DSNHATTR", "DSSIZE", "DYNAMIC", "EACH", "EDITPROC", "ELSEIF",
            "ENCODING", "END-EXEC1", "ERASE", "EXCLUDING", "EXIT", "FENCED",
            "FIELDPROC", "FILE", "FINAL", "FREE", "FUNCTION", "GENERAL",
            "GENERATED", "GRAPHIC", "HANDLER", "HOLD", "HOURS", "IF",
            "INCLUDING", "INCREMENT", "INDEX", "INHERIT", "INOUT", "INTEGRITY",
            "ISOBID", "ITERATE", "JAR", "JAVA", "LABEL", "LC_CTYPE", "LEAVE",
            "LINKTYPE", "LOCALE", "LOCATOR", "LOCATORS", "LOCK", "LOCKMAX",
            "LOCKSIZE", "LONG", "LOOP", "MAXVALUE", "MICROSECOND",
            "MICROSECONDS", "MINUTES", "MINVALUE", "MODE", "MODIFIES", "MONTHS",
            "NEW", "NEW_TABLE", "NOCACHE", "NOCYCLE", "NODENAME", "NODENUMBER",
            "NOMAXVALUE", "NOMINVALUE", "NOORDER", "NULLS", "NUMPARTS", "OBID",
            "OLD", "OLD_TABLE", "OPTIMIZATION", "OPTIMIZE", "OUT", "OVERRIDING",
            "PACKAGE", "PARAMETER", "PART", "PARTITION", "PATH", "PIECESIZE",
            "PLAN", "PRIQTY", "PROGRAM", "PSID", "QUERYNO", "READS", "RECOVERY",
            "REFERENCING", "RELEASE", "RENAME", "REPEAT", "RESET", "RESIGNAL",
            "RESTART", "RESULT", "RESULT_SET_LOCATOR", "RETURN", "RETURNS",
            "ROUTINE", "ROW", "RRN", "RUN", "SAVEPOINT", "SCRATCHPAD",
            "SECONDS", "SECQTY", "SECURITY", "SENSITIVE", "SIGNAL", "SIMPLE",
            "SOURCE", "SPECIFIC", "SQLID", "STANDARD", "START", "STATIC",
            "STAY", "STOGROUP", "STORES", "STYLE", "SUBPAGES", "SYNONYM",
            "SYSFUN", "SYSIBM", "SYSPROC", "SYSTEM", "TABLESPACE", "TRIGGER",
            "TYPE", "UNDO", "UNTIL", "VALIDPROC", "VARIABLE", "VARIANT", "VCAT",
            "VOLUMES", "WHILE", "WLM", "YEARS",
        }));
        super.setBatchLimit(defaultBatchLimit);
        selectWordSet.add("WITH");
}

 public String addCastAsType(String func,
    Val val) 
{
        String fstring = null;
        String type = getTypeName(getJDBCType(JavaTypes.getTypeCode(val
            .getType()), false));
        if (String.class.equals(val.getType()))
            type = type + "(" + characterColumnSize + ")";
        fstring = "CAST(? AS " + type + ")";
        return fstring;
}

add CAST for a function operator where operand is a param

 public  void appendCast(SQLBuffer buf,
    FilterValue val,
    int type) 
{
        // Convert the cast function: "CAST({0} AS {1})"
        int firstParam = castFunction.indexOf("{0}");
        String pre = castFunction.substring(0, firstParam); // "CAST("
        String mid = castFunction.substring(firstParam + 3);
        int secondParam = mid.indexOf("{1}");
        String post;
        if (secondParam  > -1) {
            post = mid.substring(secondParam + 3); // ")"
            mid = mid.substring(0, secondParam); // " AS "
        } else
            post = "";
        // No need to add CAST if the value is a constant
        if (val instanceof Lit || val instanceof Param) {
            buf.append(pre);
            val.appendTo(buf);
            buf.append(mid);
            buf.append(getTypeName(type));
            appendLength(buf, type);
            buf.append(post);
        } else {
            val.appendTo(buf);
            String sqlString = buf.getSQL(false);
            if (sqlString.endsWith("?")) {
                // case "(?" - convert to "CAST(? AS type"
                String typeName = getTypeName(type);
                if (String.class.equals(val.getType()))
                    typeName = typeName + "(" + characterColumnSize + ")";
                String str = "CAST(? AS " + typeName + ")";
                buf.replaceSqlString(sqlString.length() - 1,
                        sqlString.length(), str);
            }
        }
}

Cast the specified value to the specified type.

 protected  void appendLength(SQLBuffer buf,
    int type) 
{
        if (type == Types.VARCHAR)
            buf.append("(").append(Integer.toString(characterColumnSize)).
                append(")");
}

 protected  void appendSelect(SQLBuffer selectSQL,
    Object alias,
    Select sel,
    int idx) 
{
        // if this is a literal value, add a cast...
        Object val = sel.getSelects().get(idx);
        if (val instanceof Lit)
            selectSQL.append("CAST(");
        // ... and add the select per super's behavior...
        super.appendSelect(selectSQL, alias, sel, idx);
        // ... and finish the cast
        if (val instanceof Lit) {
            Class c = ((Lit) val).getType();
            int javaTypeCode = JavaTypes.getTypeCode(c);
            int jdbcTypeCode = getJDBCType(javaTypeCode, false);
            String typeName = getTypeName(jdbcTypeCode);
            selectSQL.append(" AS " + typeName);
            // if the literal is a string, use the default char col size
            // in the cast statement.
            if (String.class.equals(c))
                selectSQL.append("(" + characterColumnSize + ")");
            selectSQL.append(")");
        }
}

 protected  void appendSelectRange(SQLBuffer buf,
    long start,
    long end,
    boolean subselect) 
{
        // appends the literal range string, since DB2 is unable to handle
        // a bound parameter for it
        // do not generate FETCH FIRST clause for subselect
        if (!subselect)
            buf.append(" FETCH FIRST ").append(Long.toString(end)).
                append(" ROWS ONLY");
}

 public  void appendXmlComparison(SQLBuffer buf,
    String op,
    FilterValue lhs,
    FilterValue rhs,
    boolean lhsxml,
    boolean rhsxml) 
{
        super.appendXmlComparison(buf, op, lhs, rhs, lhsxml, rhsxml);
        if (lhsxml && rhsxml)
            appendXmlComparison2(buf, op, lhs, rhs);
        else if (lhsxml)
            appendXmlComparison1(buf, op, lhs, rhs);
        else 
            appendXmlComparison1(buf, op, rhs, lhs);
}

If this dictionary supports XML type, use this method to append xml predicate.

 public  void connectedConfiguration(Connection conn) throws SQLException 
{
    	super.connectedConfiguration(conn);
    	DatabaseMetaData metaData = conn.getMetaData();
        databaseProductName = metaData.getDatabaseProductName();
        databaseProductVersion = metaData.getDatabaseProductVersion();
        // Determine the type of DB2 database
        // First check for AS/400
        getProductVersionMajorMinorForISeries();
        if (maj  > 0) {
            if (isDB2ISeriesV5R3OrEarlier())
                db2ServerType = db2ISeriesV5R3OrEarlier;
            else if (isDB2ISeriesV5R4OrLater())
                db2ServerType = db2ISeriesV5R4OrLater;
        }
    	if (db2ServerType == 0) {
    	    if (isJDBC3(metaData)) {
    	        maj = metaData.getDatabaseMajorVersion();
    	        min = metaData.getDatabaseMinorVersion();
    	    }
    	    else
    	        getProductVersionMajorMinor();
    	    // Determine the type of DB2 database for ZOS & UDB
    	    if (isDB2UDBV81OrEarlier())
    	        db2ServerType = db2UDBV81OrEarlier;
    	    else if (isDB2ZOSV8xOrLater())
    	        db2ServerType = db2ZOSV8xOrLater;
    	    else if (isDB2UDBV82OrLater())
    	        db2ServerType = db2UDBV82OrLater;
        }
        // verify that databae product is supported
        if (db2ServerType == 0 || maj == 0)
            throw new UnsupportedException(_loc.get("db-not-supported",
                new Object[] {databaseProductName, databaseProductVersion }));                    
    	if (maj  >= 9 || (maj == 8 && min  >= 2)) {
    	    supportsLockingWithMultipleTables = true;
    	    supportsLockingWithInnerJoin = true;
    	    supportsLockingWithOuterJoin = true;
    	    forUpdateClause = "WITH RR USE AND KEEP UPDATE LOCKS";
    	    if (maj  >=9)
    	        supportsXMLColumn = true;
    	}
        // platform specific settings
        switch (db2ServerType) {
        case  db2ZOSV8xOrLater:
            // DB2 Z/OS 
            characterColumnSize = 255;
            lastGeneratedKeyQuery = "SELECT IDENTITY_VAL_LOCAL() FROM "
                + "SYSIBM.SYSDUMMY1";
            nextSequenceQuery = "SELECT NEXTVAL FOR {0} FROM "
                + "SYSIBM.SYSDUMMY1";
            sequenceSQL = "SELECT SCHEMA AS SEQUENCE_SCHEMA, "
                + "NAME AS SEQUENCE_NAME FROM SYSIBM.SYSSEQUENCES";
            sequenceSchemaSQL = "SCHEMA = ?";
            sequenceNameSQL = "NAME = ?";
            if (maj == 8)
                // DB2 Z/OS Version 8: no bigint support, hence map Java
                // long to decimal
                bigintTypeName = "DECIMAL(31,0)";
            break;
        case db2ISeriesV5R3OrEarlier:
        case db2ISeriesV5R4OrLater:
            lastGeneratedKeyQuery = "SELECT IDENTITY_VAL_LOCAL() FROM "
                + "SYSIBM.SYSDUMMY1";
            nextSequenceQuery = "SELECT NEXTVAL FOR {0} FROM "
                + "SYSIBM.SYSDUMMY1";
            validationSQL = "SELECT DISTINCT(CURRENT TIMESTAMP) FROM "
                + "QSYS2.SYSTABLES";
            sequenceSQL = "SELECT SEQUENCE_SCHEMA, "
                + "SEQUENCE_NAME FROM QSYS2.SYSSEQUENCES";
            sequenceSchemaSQL = "SEQUENCE_SCHEMA = ?";
            sequenceNameSQL = "SEQUENCE_NAME = ?";
            break;
        }
}

 public  void createIndexIfNecessary(Schema schema,
    String table,
    Column pkColumn) 
{
        if (isDB2ZOSV8xOrLater()) {
            // build the index for the sequence tables
            // the index name will the fully qualified table name + _IDX
            Table tab = schema.getTable(table);
            Index idx = tab.addIndex(tab.getFullName() + "_IDX");
            idx.setUnique(true);
            idx.addColumn(pkColumn);
        }
}

Create an index if necessary for some database tables

 public Connection decorate(Connection conn) throws SQLException 
{
        // some versions of the DB2 driver seem to default to
        // READ_UNCOMMITTED, which will prevent locking from working
        // (multiple SELECT ... FOR UPDATE statements are allowed on
        // the same instance); if we have not overridden the
        // transaction isolation in the configuration, default to
        // TRANSACTION_READ_COMMITTED
        conn = super.decorate(conn);
        if (conf.getTransactionIsolationConstant() == -1
            && conn.getTransactionIsolation() <  conn.TRANSACTION_READ_COMMITTED)
            conn.setTransactionIsolation(conn.TRANSACTION_READ_COMMITTED);
        return conn;
}

 public int getBatchLimit() 
{
        int limit = super.getBatchLimit();
        if (limit == UNLIMITED) {
            limit = defaultBatchLimit;
            if (log.isTraceEnabled())
                log.trace(_loc.get("batch_unlimit", String.valueOf(limit)));
        }
        return limit;
}

Return the batch limit. If the batchLimit is -1, change it to 100 for best performance

 public String getCastFunction(Val val,
    String func) 
{
        if (val instanceof Lit || val instanceof Param)
            if (func.indexOf("VARCHAR") == -1)
                func = addCastAsString(func, "{0}", " AS VARCHAR(1000)");
        return func;
}

Return the correct CAST function syntax

 public String[] getCreateSequenceSQL(Sequence seq) 
{
        String[] sql = super.getCreateSequenceSQL(seq);
        if (seq.getAllocate()  > 1)
            sql[0] += " CACHE " + seq.getAllocate();
        return sql;
}

 public int getDb2ServerType() 
{
        return db2ServerType;
}

 protected String getForUpdateClause(JDBCFetchConfiguration fetch,
    boolean isForUpdate,
    Select sel) 
{
        int isolationLevel;
        // For db2UDBV81OrEarlier and db2ISeriesV5R3OrEarlier:
        // "optimize for" clause appears before "for update" clause.
        StringBuffer forUpdateString = new StringBuffer(
            getOptimizeClause(sel));
        try {
            // Determine the isolationLevel; the fetch
            // configuration data overrides the persistence.xml value
            if (fetch != null && fetch.getIsolation() != -1)
                isolationLevel = fetch.getIsolation();
            else
                isolationLevel = conf.getTransactionIsolationConstant();
            if (isForUpdate) {
                switch(db2ServerType) {
                case db2ISeriesV5R3OrEarlier:
                case db2UDBV81OrEarlier:
                    if (isolationLevel == Connection.TRANSACTION_SERIALIZABLE)
                        forUpdateString.append(" ").append(forUpdateClause);
                    else 
                        forUpdateString.append(" ").append(forUpdate)
                            .append(" ").append(withRSClause);
                    break;
                case db2ZOSV8xOrLater:
                case db2UDBV82OrLater:
                    if (isolationLevel == Connection.TRANSACTION_SERIALIZABLE) {
                        forUpdateString.append(" ").append(forReadOnlyClause)
                            .append(" ").append(withRRClause)
                            .append(" ").append(useKeepUpdateLockClause);   
                    } else {
                        forUpdateString.append(" ").append(forReadOnlyClause)
                            .append(" ").append(withRSClause)
                            .append(" ").append(useKeepUpdateLockClause);                            
                    }
                    break;
                case db2ISeriesV5R4OrLater:
                    if (isolationLevel == Connection.TRANSACTION_SERIALIZABLE) {
                        forUpdateString.append(" ").append(forReadOnlyClause)
                            .append(" ").append(withRRClause)
                            .append(" ").append(useKeepExclusiveLockClause);       
                    } else {
                        forUpdateString.append(" ").append(forReadOnlyClause)
                            .append(" ").append(withRSClause)
                            .append(" ").append(useKeepExclusiveLockClause);
                    }
                    break;
                }
            }
        }
        catch (Exception e) {
            if (log.isTraceEnabled())
                log.error(e.toString(),e);
        }
        return forUpdateString.toString();
}

Get the update clause for the query based on the updateClause and isolationLevel hints

 protected String getOptimizeClause(Select sel) 
{
        if (sel != null && sel.getExpectedResultCount()  > 0) {
            StringBuffer buf = new StringBuffer();
            buf.append(" ").append(optimizeClause).append(" ")
                .append(String.valueOf(sel.getExpectedResultCount()))
                .append(" ").append(rowClause);
            return buf.toString();
        }
        return "";
}

 protected String getSequencesSQL(String schemaName,
    String sequenceName) 
{
        StringBuffer buf = new StringBuffer();
        buf.append(sequenceSQL);
        if (schemaName != null || sequenceName != null)
            buf.append(" WHERE ");
        if (schemaName != null) {
            buf.append(sequenceSchemaSQL);
            if (sequenceName != null)
                buf.append(" AND ");
        }
        if (sequenceName != null)
            buf.append(sequenceNameSQL);
        return buf.toString();
}

 public  void indexOf(SQLBuffer buf,
    FilterValue str,
    FilterValue find,
    FilterValue start) 
{
        if (find.getValue() != null) { // non constants
            buf.append("(LOCATE(CAST((");
            find.appendTo(buf);
            buf.append(") AS VARCHAR(1000)), ");
        } else {
            // this is a constant
            buf.append("(LOCATE(");
            find.appendTo(buf);
            buf.append(", ");
        }
        if (str.getValue() != null) {
            buf.append("CAST((");
            str.appendTo(buf);
            buf.append(") AS VARCHAR(1000))");
        } else {
            str.appendTo(buf);
        }
        if (start != null) {
            if (start.getValue() == null) {
                buf.append(", CAST((");
                start.appendTo(buf);
                buf.append(") AS INTEGER) + 1");
            } else {
                buf.append(", ");
                start.appendTo(buf);
            }
        }
        buf.append(") - 1)");
}

 public boolean isDB2ISeriesV5R3OrEarlier() 
{
       boolean match = false;
       if (databaseProductName != null &&
           databaseProductName.indexOf("AS") != -1
           && ((maj == 5 && min < =3) || maj <  5))
           match = true;
       return match;
}

 public boolean isDB2ISeriesV5R4OrLater() 
{
       boolean match = false;
       if (databaseProductName != null &&
           databaseProductName.indexOf("AS") != -1
           && (maj  >=6 || (maj == 5 && min  >=4)))
           match = true;
      return match;
}

 public boolean isDB2UDBV81OrEarlier() 
{
        boolean match = false;
        if (databaseProductName != null &&
            (databaseProductVersion.indexOf("SQL") != -1 
            || databaseProductName.indexOf("DB2/") != -1) &&
            ((maj == 8 && min < = 1) || maj <  8))
            match = true;
        return match;
}

 public boolean isDB2UDBV82OrLater() 
{
        boolean match = false;
        if (databaseProductName != null &&
            (databaseProductVersion.indexOf("SQL") != -1
            || databaseProductName.indexOf("DB2/") != -1)
            && ((maj == 8 && min  >= 2) || (maj  >= 9)))
            match = true;
        return match;
}

 public boolean isDB2ZOSV8xOrLater() 
{
       boolean match = false;
       if (databaseProductName != null &&
           (databaseProductVersion.indexOf("DSN") != -1
           || databaseProductName.indexOf("DB2/") == -1)
           && maj  >= 8)
           match = true;
        return match;
}

 public OpenJPAException newStoreException(String msg,
    SQLException[] causes,
    Object failed) 
{
        if (causes != null && causes.length  > 0)
            msg = appendExtendedExceptionMsg(msg, causes[0]);
        return super.newStoreException(msg, causes, failed);
}

 public boolean supportsRandomAccessResultSet(Select sel,
    boolean forUpdate) 
{
        return !forUpdate
            && super.supportsRandomAccessResultSet(sel, forUpdate);
}