source suite/ibmdb2i/include/have_ibmdb2i.inc; source suite/ibmdb2i/include/have_i61.inc; --disable_warnings drop table if exists t1, ffd, fd; --enable_warnings --disable_abort_on_error --error 0,255 exec system "DLTF QGPL/FFDOUT" > /dev/null; --error 0,255 exec system "DLTF QGPL/FDOUT" > /dev/null; --enable_abort_on_error let $count= query_get_value(select count(*) from information_schema.COLLATIONS where COLLATION_NAME <> "binary", count(*),1); while ($count) { let $collation = query_get_value(select COLLATION_NAME from information_schema.COLLATIONS where COLLATION_NAME <> "binary" order by COLLATION_NAME desc, COLLATION_NAME, $count); error 0,1005,2504,2028; eval CREATE TABLE t1 ($collation integer, c char(10), v varchar(20), index(c), index(v)) collate $collation engine=ibmdb2i; if (!$mysql_errno) { insert into t1 (c,v) values ("abc","def"),("abcd", "def"),("abcde","defg"),("aaaa","bbbb"); insert into t1 select * from t1; explain select c,v from t1 force index(c) where c like "ab%"; explain select c,v from t1 force index(v) where v like "de%"; drop table t1; eval create table t1 ($collation char(10) primary key) collate $collation engine=ibmdb2i; system system "DSPFFD FILE(\"test\"/\"t1\") OUTPUT(*OUTFILE) OUTFILE(QGPL/FFDOUT) OUTMBR(*FIRST *ADD)" > /dev/null; system system "DSPFD FILE(\"test\"/\"t1\") TYPE(*SEQ) OUTPUT(*OUTFILE) OUTFILE(QGPL/FDOUT) OUTMBR(*FIRST *ADD)" > /dev/null; drop table t1; } dec $count; } create table ffd (WHCHD1 CHAR(20), WHCSID decimal(5,0)) engine=ibmdb2i; system system "CPYF FROMFILE(QGPL/FFDOUT) TOFILE(\"test\"/\"ffd\") mbropt(*replace) fmtopt(*drop *map)" > /dev/null; create table fd (SQSSEQ CHAR(10)) engine=ibmdb2i; system system "CPYF FROMFILE(QGPL/FDOUT) TOFILE(\"test\"/\"fd\") mbropt(*replace) fmtopt(*drop *map)" > /dev/null; create temporary table intermed (row integer key auto_increment, cs char(30), ccsid integer); insert into intermed (cs, ccsid) select * from ffd; create temporary table intermed2 (row integer key auto_increment, srtseq char(10)); insert into intermed2 (srtseq) select * from fd; select ccsid, cs, srtseq from intermed inner join intermed2 on intermed.row = intermed2.row; drop table ffd, fd;