(520) Create extra indexes in the target database

Create sufficient indexes to get better query performance. The index creation could be defined in system table QMIRROR.ZTBSQL.

Unique keys of physical file will be kept in field QMIRROR.ZAMTBF.TBUNIQ.

QMirror/DB will insert a record into system table QMIRROR.ZTBSQL to build up index by pulling index keys off of the AS400 physical file with ZQSEQ=99998 after the table was downloaded.

QMirror/DB will insert a records into system table QMIRROR.ZTBSQL to build up indexes by pulling index keys off of the AS400 logical files with ZQSEQ=99999 when you take command option 5 to build up indexes from AS400 logical files.

You can create additional indexes by manually inserting records into system table QMIRROR.ZTBSQL. ZQSEQ set to 9999x, such as 99999 or 99998, is reserved by QMirror.

Example for Oracle, MySQL, PostgreSQL, and MaxDB:

INSERT INTO QMIRROR.ZTBSQL (ZQNDX, ZQBOA, ZQKEY, ZQSEQ, ZQSQL)
VALUES (101, ‘A’, ‘LIBRARY1.FILEA;’,1,
‘CREATE INDEX FILEA01 ON LIBRARY1.FILEA (FIELD1, FIELD2)’)

Example for MS SQL Server:

INSERT INTO QMIRROR..ZTBSQL (ZQNDX, ZQBOA, ZQKEY, ZQSEQ, ZQSQL)
VALUES (101, ‘A’, ‘LIBRARY1..FILEA;’, 1,
‘USE LIBRARY1; CREATE INDEX FILEA01 ON FILEA (FIELD1, FIELD2)’)

(Notes: Too many indexes on PC databases may slow down QMirror replication performance.)

Previous (510) Grant target database privileges to users
Next (525) Dynamic table selection