SQL0107 Errors with Reports from the SQL Performance Monitor (Operations Navigator)

SQL0107 Errors with Reports from the SQL Performance Monitor (Operations Navigator)

Post by Herbert Groot Jebbin » Sat, 09 Oct 1999 04:00:00



Hi,

When I try to execute a SQL Performance Monitor report inside
Operations Navigator I get a lot of SQL0107 errors.

SQL0107: "Aantal items in gemaakte index" too long. Maximum 30 characters.

Cause . . . . . :   The name or string beginning with "Aantal items in
gemaakte index" is too long.  The maximum length allowed is 30. The
maximum length for names depend on the type of the name: -- System names
for a collection, library, package, program, and other system objects
cannot exceed 10 characters. -- SQL names for a table, view, index,
constraint, correlation, or parameter cannot exceed 128 characters. --
SQL names for a column cann exceed 30 characters. System-column names
cannot exceed 10 characters. -- Cursor names, statement names, or
relational database names cannot exceed 18 characters. -- Procedure names
cannot exceed 128 characters. If the external program name is not
specified, the procedure name cannot exceed 10 characters because it is
used for the program name. -- Host variable names cannot exceed 64
characters. -- Names for SQL variables, parameters, and labe in the
routine body of an SQL procedure cannot exceed 64 characters. The maximum
length of the string for a COMMENT ON or LABEL
 ON statement depends on the statement. -- Comments on columns and tables
cannot exceed 2000 characters. -- Labels for column headings cannot
exceed 60 characters. -- Labels for tables or column text cannot exceed
50 characters.

Recovery  . . . :   Change the name or string to a length of 30. Try the
request again.

It seems that the real maximum lenght is 28 and not 30, the first
statement below works, the second gives the SQL0107 error.

select field as "1234567890123456789012345678" from library.file
select field as "12345678901234567890123456789" from library.file

Here is the generated SQL statement.

SELECT
 /* Databaseprestatiemonitor Informatie over gemaakte indexen */

a.QQTIME as "Tijd",

 /* Informatie over gemaakte indexen */
DECIMAL(QQTTIM/1000,18,3) as "Tijdsduur index maken",
QQRIDX as "Aantal items in gemaakte index",
CASE QQIDXA
WHEN 'Y' THEN 'Ja'
WHEN 'N' THEN 'Nee'
ELSE QQIDXA
END as "Geadviseerde index",

CASE QQRCOD
WHEN 'I1' THEN 'recordselectie'
WHEN 'I2' THEN 'ORDER BY of GROUP BY'
WHEN 'I3' THEN 'Recordselectie en ORDER BY of GROUP BY'
WHEN 'I4' THEN 'Geneste-lusjoin'
WHEN 'I5' THEN 'Recordselectie met bitmapverwerking'
ELSE QQRCOD
END as "Retourcode",
QQCRTK as "Gemaakte indexsleutels",
QQIDXK as "Geadv. primaire indexsleutels",
QQIDXD as "Geadviseerde kolommen in index",
QQLTLN as "Bibliotheek tabel/view",
QQLTFN as "Naam tabel/view",
QQLPTL as "Bibliotheek basistabel",
QQLPTF as "Naam basistabel",
QQLILN as "Bibliotheek index",
QQLIFN as "Naam index",
QQLNLN as "NLSS-bibliotheek",
QQLNTN as "NLSS-tabel",
QQQDTN as "Aantal beperkte SELECT-instr.",
QQQDTL as "Genest niv. bep. SELECT-instr.",
QQMATN as "View bep. SELECT realiseren",
QQMATL as "View genest niveau realiseren",
d.QQTOTR as "Aantal rijen in basistabel",
QQREST as "Schatting aant. gesel. rijen",
QQFKEY as "Geschatte positiesleutels",
QQKSEL as "Geschatte selectiesleutels",
QQAJN as "Schatting aantal Join-rijen",
QQJNP as "Positie van Join",
QQJNDS as "Aantal gegevensruimten",
CASE QQJNMT
WHEN 'NL' THEN 'Geneste lus'
WHEN 'MF' THEN 'Geneste lus met selectie'
WHEN 'HJ' THEN 'Join met hash'
ELSE QQJNMT
END as "Joinmethode",
CASE QQJNTY
WHEN 'IN' THEN 'Join van een lager niveau'
WHEN 'PO' THEN 'Linkerjoin van een hoger niveau'
WHEN 'EX' THEN 'Exceptionjoin'
ELSE QQJNTY
END as "Type Join",
CASE QQJNOP
WHEN 'EQ' THEN 'Gelijk aan'
WHEN 'NE' THEN 'Niet gelijk aan'
WHEN 'GT' THEN 'Groter dan'
WHEN 'GE' THEN 'Groter dan of gelijk aan'
WHEN 'LT' THEN 'Kleiner dan'
WHEN 'LE' THEN 'Kleiner dan of gelijk aan'
WHEN 'CP' THEN 'Cartesisch product'
ELSE QQJNOP
END as "Joinoperator",
DECIMAL(QQEPT/1000,18,3) as "Schatting verwerkingstijd",
CASE QQKP
WHEN 'Y' THEN 'Ja'
WHEN 'N' THEN 'Nee'
ELSE QQKP
END as "Sleutelpositionering",
QQKPN as "Aantal positioneringssleutels",
CASE QQKS
WHEN 'Y' THEN 'Ja'
WHEN 'N' THEN 'Nee'
ELSE QQKS
END as "Sleutelselectie",
CASE QQDSS
WHEN 'Y' THEN 'Ja'
WHEN 'N' THEN 'Nee'
ELSE QQDSS
END as "Selectie gegevensruimten",
CASE QQCST
WHEN 'Y' THEN 'Ja'
WHEN 'N' THEN 'Nee'
ELSE QQCST
END as "Gebruikte sleutelbeperkingen",
QQCSTN as "Naam sleutelbeperking",

 /* Systeemnamen */
QQTLN as "Bibliotheek systeemtabel/-view",
QQTFN as "Naam systeemtabel/-view",
QQPTLN as "Bibliotheek basistabel systeem",
QQPTFN as "Basistabel systeem",
QQILNM as "Bibliotheek systeemindex",
QQIFNM as "Naam systeemindex",
QQNTNM as "NLSS-tabel van systeem",
QQNLNM as "NLSS-bibliotheek van systeem",

 /* Kosten */
DECIMAL(QQMAXT/1000,18,3) as "Maximale uitvoeringstijd",
DECIMAL(QQAVGT/1000,18,3) as "Gemiddelde uitvoeringstijd",
DECIMAL(QQMINT/1000,18,3) as "Minimale uitvoeringstijd",
DECIMAL(QQOPNT/1000,18,3) as "Maximale tijdsduur voor openen",
DECIMAL(QQFETT/1000,18,3) as "Maximale tijdsduur voor ophalen",
DECIMAL(QQCLST/1000,18,3) as "Maximale tijdsduur voor afsluiten",
DECIMAL(QQOTHT/1000,18,3) as "Maximale tijdsduur andere acties",
QQMETU as "Meest kostbare gebruik",
QQLTU  as "Laatste gebruik",

 /* Identificatie instructie */
CASE QQSTOP
WHEN 'AL' THEN 'ALTER TABLE'
WHEN 'CA' THEN 'CALL'
WHEN 'CD' THEN 'CREATE DISTINCT TYPE'
WHEN 'CF' THEN 'CREATE FUNCTION'
WHEN 'CL' THEN 'CLOSE'
WHEN 'CO' THEN 'COMMENT ON'
WHEN 'CM' THEN 'COMMIT'
WHEN 'CN' THEN 'CONNECT'
WHEN 'CC' THEN 'CREATE COLLECTION'
WHEN 'CI' THEN 'CREATE INDEX'
WHEN 'CP' THEN 'CREATE PROCEDURE'
WHEN 'CS' THEN 'CREATE ALIAS'
WHEN 'CT' THEN 'CREATE TABLE'
WHEN 'CV' THEN 'CREATE VIEW'
WHEN 'DC' THEN 'DECLARE CURSOR'
WHEN 'DD' THEN 'DELETE...DELETE'
WHEN 'DE' THEN 'DESCRIBE'
WHEN 'DF' THEN 'DELETE...FETCH'
WHEN 'DI' THEN 'DISCONNECT'
WHEN 'DK' THEN 'DELETE...CLOSE'
WHEN 'DL' THEN 'DELETE'
WHEN 'DP' THEN 'DECLARE PROCEDURE'
WHEN 'DR' THEN 'DROP'
WHEN 'DT' THEN 'DESCRIBE TABLE'
WHEN 'DU' THEN 'DELETE...UPDATE'
WHEN 'EX' THEN 'EXECUTE'
WHEN 'EI' THEN 'EXECUTE IMMEDIATE'
WHEN 'FC' THEN 'FETCH...CLOSE'
WHEN 'FD' THEN 'FETCH...DELETE'
WHEN 'FE' THEN 'FETCH'
WHEN 'FF' THEN 'FETCH...FETCH'
WHEN 'FL' THEN 'FREE LOCATOR'
WHEN 'FU' THEN 'FETCH...UPDATE'
WHEN 'GR' THEN 'GRANT'
WHEN 'IC' THEN 'INSERT'
WHEN 'IN' THEN 'INSERT'
WHEN 'LO' THEN 'LABEL ON'
WHEN 'LK' THEN 'LOCK'
WHEN 'OC' THEN 'OPEN...CLOSE'
WHEN 'OD' THEN 'OPEN...DELETE'
WHEN 'OF' THEN 'OPEN...FETCH'
WHEN 'OP' THEN 'OPEN'
WHEN 'OU' THEN 'OPEN...UPDATE'
WHEN 'PR' THEN 'PREPARE'
WHEN 'RE' THEN 'RELEASE'
WHEN 'RT' THEN 'RENAME'
WHEN 'RV' THEN 'REVOKE'
WHEN 'RO' THEN 'ROLLBACK'
WHEN 'SI' THEN 'SELECT INTO'
WHEN 'SC' THEN 'SET CONNECTION'
WHEN 'SK' THEN 'SELECT INTO'
WHEN 'SP' THEN 'SET PATH'
WHEN 'SR' THEN 'SET RESULTS'
WHEN 'ST' THEN 'SET TRANSACTION'
WHEN 'SV' THEN 'SET VARIABLE'
WHEN 'UC' THEN 'UPDATE...CLOSE'
WHEN 'UD' THEN 'UPDATE...DELETE'
WHEN 'UF' THEN 'UPDATE...FETCH'
WHEN 'UP' THEN 'UPDATE'
WHEN 'UU' THEN 'UPDATE...UPDATE'

ELSE QQSTOP
END as "Bewerking",
QQCNT as "Telling herhaalde instructies",
varchar(b.qqsttx,20000) as "Instructietekst",
varchar(qqhvar,500) as "Waarden hostvariabelen",

 /* Aantal keren geopend */
QQFULO as "Volledig geopend",
QQPSUO as "Gedeeltelijk geopend",

 /* Rijgrootten */
a.QQTOTR as "Tabelrijen",
QQRROW as "Resultaatrijen",
QQARSS as "Gemiddelde grootte resultaat",

 /* Implementatie */
CASE QQODPI
WHEN 'R' THEN 'Opnieuw bruikbaar'
WHEN 'N' THEN 'Niet opnieuw bruikbaar'
ELSE QQODPI
END as "ODP-implementatie",

CASE QQHVI
WHEN 'I' THEN 'ISV'
WHEN 'V' THEN 'V2'
WHEN 'U' THEN 'UP'
ELSE QQHVI
END as "Implementatie hostvariabele",

CASE QQDACV
WHEN 'N' THEN NULL
WHEN '0' THEN NULL
WHEN '1' THEN 'Afwijkende lengten'
WHEN '2' THEN 'Afwijkende numerieke typen'
WHEN '3' THEN 'Met null afgesloten C-variabele'
WHEN '4' THEN 'Variabele lengte/vaste lengte'
WHEN '5' THEN 'CCSID-conversie'
WHEN '6' THEN 'DRDA-toewijzing vereist'
WHEN '7' THEN 'Kolom Datum/tijd'
WHEN '8' THEN 'Te veel hostvariabelen'
WHEN '9' THEN 'Doeltabel is geen SQL-tabel'
ELSE QQDACV
END as "Gegevensconversie",
QQCTS as "Aantal tabelzoekbewerkingen",

 /* Informatie over index */
QQCIU  as "Telling indexgebruik",
QQCIC  as "Telling gemaakte indexen",
QQCIA  as "Aantal geadviseerde indexen",

 /* Kopie van gegevens */
QQCTF  as "Aantal tijdelijke tabellen",
QQCSO  as "Aantal sorteringen",

 /* Heropbouw toegangsmethode */
QQAPR as "Heropbouw toegangsmethode",
QQCAPR as "Telling heropbouw toegangsmeth.",
QQAPRT as "Laatste opbouw toegangsmeth.",

 /* Identificatie taak-gebruiker-programma */
QQJOB  as "Taak",
QQUSER as "Taakgebruiker",
QQJNUM as "Taaknummer",
QQTHID as "Thread-ID",
QQPLIB as "Programmabibliotheek",
QQPNAM as "Programma",

 /* Instructiekenmerken */
QQUDEF as "Door gebruiker gedef. veld",
QQCNAM as "Cursor",
QQSNAM as "Naam instructie"

FROM HGJSQLMON.QPM0000018 a left join HGJSQLMON.QPM0000019 b on
a.QQKEY=b.QQKEY
left join HGJSQLMON.QPM0000010 c on a.QQKEY=c.QQKEY
JOIN HGJSQLMON.QPM0000002 d on a.QQKEY=d.QQKEY

ORDER BY "Tijdsduur index maken" DESC

Greetings, Herbert

--
Find AS/400 information: http://www.xs4all.nl/~hgj/find400.html
IBM & Linux: http://www.xs4all.nl/~hgj/hin/
The Trans-Siberian Railroad Page: http://www.xs4all.nl/~hgj/

 
 
 

SQL0107 Errors with Reports from the SQL Performance Monitor (Operations Navigator)

Post by Kent Milliga » Tue, 12 Oct 1999 04:00:00


The delimiters->" do count as part of the name length.  You're correct, if
delimiters are used then the name length is only 28.

--
Kent Milligan, DB2 & Business Intelligence team
AS/400 Partners In Development

(opinions stated are not necessarily those of my employer)

 
 
 

SQL0107 Errors with Reports from the SQL Performance Monitor (Operations Navigator)

Post by Herbert Groot Jebbin » Tue, 12 Oct 1999 04:00:00



> The delimiters->" do count as part of the name length.
> You're correct, if delimiters are used then the name
> length is only 28.

Ok, thats fine, only those lines are generated by the SQL
Performance Monitor Tool report function inside Operations
Navigator. Building a database with SQL performance data
is great, call me greedy, I want the reports also.

Greetings, Herbert

 
 
 

SQL0107 Errors with Reports from the SQL Performance Monitor (Operations Navigator)

Post by Charles R. Penc » Wed, 13 Oct 1999 04:00:00




> > The delimiters->" do count as part of the name length.
> > You're correct, if delimiters are used then the name
> > length is only 28.

> Ok, thats fine, only those lines are generated by the SQL
> Performance Monitor Tool report function inside Operations
> Navigator. Building a database with SQL performance data
> is great, call me greedy, I want the reports also.

Probably a translation error, whereby a restriction to translate to 28
characters-or-less was either unstated, or was not adhered to.  You should report
this error to your service provider.

Regards, Chuck
All comments provided "as is" with no warranties of any kind whatsoever.

 
 
 

SQL0107 Errors with Reports from the SQL Performance Monitor (Operations Navigator)

Post by Kent Milliga » Thu, 14 Oct 1999 04:00:00


I had misread your post, I thought it was an SQL Performance Monitor
report/query that you created.  I agree with Chuck that you should report this
as a defect.  I've also made the development team aware of this problem.

--
Kent Milligan, DB2 & Business Intelligence team
AS/400 Partners In Development

(opinions stated are not necessarily those of my employer)

 
 
 

1. problems creating sql-procedures with operations navigator

every time i try to run some sql from within operations-navigator i get
error SQL7032 back. I can do a "DROP TABLE" or "CREATE TABLE". But when
it comes to "SELECT" or "UPDATE" i get the error-message back.
All the necessary programs are installed on the AS400 (V4R3M0). As far
as i know the latest patches have been applied.
Did someone manage to get this running?
plz help
sincerely
Juergen Sommer
(ASC GmbH)

Sent via Deja.com
http://www.deja.com/

2. changing housecodes on remote

3. sql performance monitor

4. DEC printer escape codes -> PostScript/ASCII

5. NET.DATA errors errors and more errors

6. workaround count AND the lack of subqueries

7. review monitor on operation navigator v4r5

8. connecting 5 PC's through 1 modem to a bbs

9. Operations Navigator (IPDS files)

10. PPP PTP definition error using operations navigator

11. EXITPGM in Backup-Procedure (e.g. Operations Navigator)

12. C/A, Operations Navigator, and ODBC with Managed data source