User:PoC

I am the principal maintainer of this Wiki.

Unofficial Benchmarks

For getting a rough idea about CPU speed, I have a physical table about German ZIP codes, including long distance telephone prefixes, filled with nearly 20,000 records in six fields, summing up to 2.25 MiB of data. From the original source in the internet, there were a lot of duplicate records. Birgitta Hauser came up with a quick and dirty idea how to eliminate these dupes.

Since the SQL proved to be running really slow, I came up to use it as a benchmark. Of course, it's not 100  fair, because of different OS releases and accompanying improvements in SQL handling. But it gives a rough idea.

The SQL was run from a source PF member as standard batch job (default parameters) on idling machines (no additional work).

Machine	Proc. Feat.	OS	Runtime (ca.)	Comments
9401-150	2270	V4R5	About 118 hrs.
9406-S20	2161	V4R4	About 13.5 hrs.
9406-800	7408	V5R2	About 2 hrs.
8203-E4A	5587	V7R2	1-2 Minutes	LPAR with two CPU cores assigned.

---

Used files

• DDS for the database as follows:

A                                      UNIQUE
A          R PLZTBL
A            ORT           32A
A            LAND          17A
A            VORWAHL        6A         ALWNULL
A            PLZ            5A
A            ID             5S 0
A            ZUSATZ        30A         ALWNULL
A          K ID

• SQL for RUNSQLSTM as follows:

DELETE FROM PLZPF A
 WHERE A.ID = (
  SELECT MAX(ID) FROM PLZPF
   GROUP BY ORT, LAND, VORWAHL, PLZ, ZUSATZ
   HAVING COUNT(*) > 1 AND MAX(ID) = A.ID)

• Submit Command for RUNSQLSTM as follows:

SBMJOB JOB(DLTPLZDUPS) CMD(RUNSQLSTM SRCFILE(PLZ/SOURCES) SRCMBR(DLTDUPS) COMMIT(*NONE) NAMING(*SQL) DFTRDBCOL(PLZ))

I can provide a save file of the whole library on request.

The switchback to the traditional CQE SQL Engine involves creating a copy of QSYS/QAQQINI, updating this copy through SQL, SQE_NATIVE_ACCESS.

Weblinks

• Creating a QAQQINI File
• Change Query Attributes for using the desired QAQQINI file
• QAQQINI File Format