SYSROUTINE & SYSPARMS missing in QSYS2

[sintaq]

Hi all,

I in V5R4 box and try to create some SQL functions. However it failed due to SYSROUTINE & SYSPARMS are not existed in my QSYS2 library.
Shouldnt these files by default should be there? Or is there something I missing?

Thank you

[DeadManWalks]

Re: SYSROUTINE & SYSPARMS missing in QSYS2

I can't say for 5.4 but I see em there (6.1). Do you see them any where on the box?

[tomliotta]

Re: SYSROUTINE & SYSPARMS missing in QSYS2

I in V5R4 box and try to create some SQL functions. However it failed due to SYSROUTINE & SYSPARMS are not existed in my QSYS2 library.
Shouldnt these files by default should be there? Or is there something I missing?

You should see them under most circumstances, at least at V5R3 and later. I don't have info for earlier to know when they first showed up.

Anything like this problem should go through IBM Support. It's never certain what actual problem is happening that results in what you're seeing. But in cases where IBM Support isn't available and you're reasonably confident you know what you're doing, there are steps you can take that are pretty solid.

First thing to do:

CALL PGM(QSQIBMCHK)

Numerous messages will show in the joblog a few seconds later. DB2 catalog objects will be located and validated. Any that are missing will be logged. It probably won't help to run this, but it can give a quick image of the full state of your system DB2 catalog. The two objects you're missing should show as missing, but others also might. (If nothing shows as missing, then you probably should think in terms of an authority problem or something else that is interfering with accessing them.)

That API has been around since PTFs added it to V5R3 and V5r4. If you don't have it, I can probably track down the PTF number; but a Google search is really all that's needed.

If any objects are shown as missing from QSYS2, they can generally be generated. To do that, you need to be in restricted state. (This next API can be run when the system is active, but don't expect anything but "unpredictable" results that will probably not be pleasant. And then you'll have to go to restricted state to fix things anyway.):

CALL PGM(QSYS2/QSQXRLF) PARM('DLT' 'QSYS2     ')
CALL PGM(QSYS2/QSQXRLF) PARM('CRT' 'QSYS2     ')

On an older small V5R3 model 170, those run in less than 8 minutes. Sorry, but I can't test on any larger more current and probably faster system. I'd expect it to take a bit longer for a large database, but also much faster than the 170. It should mostly simply be creating VIEWs, etc., so data volume shouldn't matter as much.

Note that the second parameter names a SCHEMA. The API can be run for user schemas, but user catalog objects generally really shouldn't be needed. They should only be subsets of the system-level objects.

At least one other API might be useful or even needed:

CALL PGM(QSQSYSIBM)

That's similar to QSQXRLF except instead of working in QSYS2, it generates SYSIBM objects.

[sintaq]

Re: SYSROUTINE & SYSPARMS missing in QSYS2

I can't say for 5.4 but I see em there (6.1). Do you see them any where on the box?

Nope. WRKOBJ *ALL with admin ID also doesnt reveal anything.

[sintaq]

Re: SYSROUTINE & SYSPARMS missing in QSYS2

Tom,

Here is the result from the 1st command (thanx, learned new command for health check)
I able to restore those 2 files SYSPARMS and SYSROUTINE from a system that i dont have admin ID. I just backup (using WRKQRY > Create new file with empty data) those files and FTP to the problematic box and restored them. Solved part of the problem but I cant use this trick for LFs.

CALL PGM(QSQIBMCHK)
QSQIBMCHK - BEGIN OBJECT VERIFICATION
MISSING - PROCEDURE-QSYS2/ANALYZE_PLAN_CACHE, EXPECTED 2, FOUND 0
MISSING - PROCEDURE-QSYS2/COMPARE_MONITOR, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-QSYS2/DUMP_PLAN_CACHE, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-QSYS2/QCMDEXC, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-QSYS2/HEALTH_DATABASE_OVERVIEW, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-QSYS2/HEALTH_DESIGN_LIMITS, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-QSYS2/HEALTH_SIZE_LIMITS, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-QSYS2/JOBSUMMARY, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-QSYS2/LIST_EXPLAINABLE_DETAILED, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-QSYS2/PROCESS_DETAILED_MONITOR, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-QSYS2/PRINT_QUERY_DEFINITION, EXPECTED 1, FOUND 0
MISSING - FUNCTION-QSYS2/COLUMN_STATISTICS, EXPECTED 1, FOUND 0
MISSING - FUNCTION-QSYS2/CONDENSE_ADVICE, EXPECTED 1, FOUND 0
MISSING - FUNCTION-QSYS2/INDEX_PARTITION_STATISTICS, EXPECTED 1, FOUND 0
MISSING - FUNCTION-QSYS2/PARTITION_STATISTICS, EXPECTED 1, FOUND 0
MISSING - FUNCTION-QSYS2/QDBDGLIM, EXPECTED 1, FOUND 0
MISSING - FUNCTION-QSYS2/QDBRGZMV, EXPECTED 1, FOUND 0
MISSING - FUNCTION-QSYS2/QDBSZLIM, EXPECTED 1, FOUND 0
MISSING - FUNCTION-QSYS2/QMPROFILES, EXPECTED 1, FOUND 0
MISSING - FUNCTION-QSYS2/RAISE_ERROR, EXPECTED 1, FOUND 0
MISSING - FUNCTION-QSYS2/SCHEMAS, EXPECTED 1, FOUND 0
MISSING - FUNCTION-QSYS2/SQLGETAUTH, EXPECTED 1, FOUND 0
MISSING - FUNCTION-QSYS2/SQLGETVIEW, EXPECTED 1, FOUND 0
MISSING - FUNCTION-QSYS2/USERS, EXPECTED 1, FOUND 0
MISSING - TABLE-QSYS2/SYSCHARSETS, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/AUTHORIZATIONS, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/CATALOG_NAME, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/CHARACTER_SETS, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/CHARACTER_SETS_S, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/CHECK_CONSTRAINTS, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/COLUMNS, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/COLUMNS_S, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/CONDENSEDINDEXADVICE, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/INFORMATION_SCHEMA_CATALOG_NAME, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/PARAMETERS, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/PARAMETERS_S, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/REF_CONSTRAINTS, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/REFERENTIAL_CONSTRAINTS, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/ROUTINES, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/ROUTINES_S, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/SCHEMATA, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/SCHEMATA_S, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/SQL_LANGUAGES_S, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/SQLQMPROFILES, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/SYSCOLUMNSTAT, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/SYSPARTITIONINDEXSTAT, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/SYSPARTITIONSTAT, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/SYSTABLEINDEXSTAT, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/SYSTABLESTAT, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/TABLE_CONSTRAINTS, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/TABLE_CONSTRAINTS, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/TABLES, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/TABLES_S, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/UDT_S, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/UDT_S, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/USER_DEFINED_TYPES, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/USER_DEFINED_TYPES, EXPECTED 1, FOUND 0
MISSING - VIEW-QSYS2/VIEWS, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSPROC/DBG_ENDSESSIONMANAGER, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSPROC/DBG_INITIALIZECLIENT, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSPROC/DBG_LOOKUPSESSIONMANAGER, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSPROC/DBG_PINGSESSIONMANAGER, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSPROC/DBG_RECVCLIENTREPORTS, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSPROC/DBG_RUNSESSIONMANAGER, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSPROC/DBG_SENDCLIENTCOMMANDS, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSPROC/DBG_SENDCLIENTREQUESTS, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSPROC/DBG_TERMINATECLIENT, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSIBM/SQLCAMESSAGE, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSIBM/SQLCOLPRIVILEGES, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSIBM/SQLCOLUMNS, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSIBM/SQLFOREIGNKEYS, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSIBM/SQLFUNCTIONS, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSIBM/SQLFUNCTIONCOLS, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSIBM/SQLGETTYPEINFO, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSIBM/SQLPRIMARYKEYS, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSIBM/SQLPROCEDURECOLS, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSIBM/SQLPROCEDURES, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSIBM/SQLSPECIALCOLUMNS, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSIBM/SQLSTATISTICS, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSIBM/SQLTABLEPRIVILEGES, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSIBM/SQLTABLES, EXPECTED 1, FOUND 0
MISSING - PROCEDURE-SYSIBM/SQLUDTS, EXPECTED 1, FOUND 0
MISSING - FUNCTION-SYSIBM/CPRIVILEGES, EXPECTED 1, FOUND 0
MISSING - FUNCTION-SYSIBM/PRIVILEGES, EXPECTED 1, FOUND 0
MISSING - FUNCTION-SYSIBM/SCHEMAS, EXPECTED 1, FOUND 0
MISSING - FUNCTION-SYSIBM/SQLGETAUTH, EXPECTED 1, FOUND 0
MISSING - FUNCTION-SYSIBM/SQLGETVIEW, EXPECTED 1, FOUND 0
MISSING - FUNCTION-SYSIBM/USERS, EXPECTED 1, FOUND 0
MISSING - TABLE-SYSIBM/SQLTYPEINFO, EXPECTED 1, FOUND 0
MISSING - TABLE-SYSIBM/SYSCHARSETS, EXPECTED 1, FOUND 0
MISSING - TABLE-SYSIBM/SYSDUMMY1, EXPECTED 1, FOUND 0
MISSING - TABLE-SYSIBM/SYSPRIVILEGES, EXPECTED 1, FOUND 0
MISSING - TABLE-SYSIBM/SYSTABLETYPES, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/AUTHORIZATIONS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/CATALOG_NAME, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/CHARACTER_SETS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/CHARACTER_SETS_S, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/CHECK_CONSTRAINTS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/COLUMNS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/COLUMNS_S, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/INFORMATION_SCHEMA_CATALOG_NAME, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/PARAMETERS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/PARAMETERS_S, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/REF_CONSTRAINTS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/REFERENTIAL_CONSTRAINTS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/ROUTINES, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/ROUTINES_S, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SCHEMATA, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SCHEMATA_S, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SQL_LANGUAGES_S, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SQLCOLPRIVILEGES, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SQLCOLUMNS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SQLFOREIGNKEYS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SQLFUNCTIONCOLS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SQLFUNCTIONS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SQLPRIMARYKEYS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SQLPROCEDURECOLS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SQLPROCEDURES, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SQLSCHEMAS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SQLSPECIALCOLUMNS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SQLSTATISTICS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SQLTABLEPRIVILEGES, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SQLTABLES, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SQLTABLETYPES, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SQLUDTS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SYSJARCONTENTS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/SYSJAROBJECTS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/TABLE_CONSTRAINTS, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/TABLES, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/TABLES_S, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/UDT_S, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/USER_DEFINED_TYPES, EXPECTED 1, FOUND 0
MISSING - VIEW-SYSIBM/VIEWS, EXPECTED 1, FOUND 0
VALIDATED - TABLE-QUSRSYS/QAUGDBLL
VALIDATED - TABLE-QUSRSYS/QAUGDBNAV
VALIDATED - TABLE-QUSRSYS/QAUGDBPMD
VALIDATED - TABLE-QUSRSYS/QAUGDBPMD2
VALIDATED - VIEW-QUSRSYS/QAUGDBVW
TOTAL IBM OBJECTS FOUND = 5
TOTAL IBM OBJECTS UNKNOWN = 0
TOTAL IBM OBJECTS MISSING = 133
QSQIBMCHK - OBJECT VERIFICATION COMPLETE

looks like a looonnnnggg list of missing items

[tomliotta]

Re: SYSROUTINE & SYSPARMS missing in QSYS2

I able to restore those 2 files SYSPARMS and SYSROUTINE from a system that i dont have admin ID. I just backup (using WRKQRY > Create new file with empty data) those files and FTP to the problematic box and restored them.

DB2 files should not be restored from a different system, and they should only be created by DB2, not by Query/400. The content is not likely to be correct, and the basic file attributes will not be correct. They might work for most purposes, but there is too great of a chance that they will introduce a failure that will be unpredictable when it happens and also be very difficult to diagnose.

The files should be created only by DB2 or through instructions from IBM.

[sintaq]

Re: SYSROUTINE & SYSPARMS missing in QSYS2

DB2 files should not be restored from a different system, and they should only be created by DB2, not by Query/400. The content is not likely to be correct, and the basic file attributes will not be correct. They might work for most purposes, but there is too great of a chance that they will introduce a failure that will be unpredictable when it happens and also be very difficult to diagnose.

The files should be created only by DB2 or through instructions from IBM.

Opss, my bad. Noted

Tom,
Can I execute the 2nd commands and subsequently 3rd comman? And what is restricted state? (sorry, noob question)

[tomliotta]

Re: SYSROUTINE & SYSPARMS missing in QSYS2

First, I have to emphasize again that this is a problem for IBM Support. Actions like this should not be taken from forum comments due to how pervasive the effects can be. Support should review the environment data.

If Support is not available to you, you might not have any choice.

(sorry, noob question)

And this isn't work that a 'noob' should be doing.

But again, you might not have any choice.

If I were doing it, I would run the IBM APIs before trying to create the objects myself. I can't recommend that you should do the same; I can only say what I would do. Anyone else who can comment might say what they would do.

The 2nd and 3rd commands are the ones I would run.

And what is restricted state?

In short, that's the state the system is in when all subsystems are ended. The controlling subsystem (usually QCTL or QBASE) will be in *END status, and the only active job other than system jobs that do not run in subsystems will usually be the interactive job at the system console. All TCP/IP and host servers are ended, and all subsystems are ended. The final command to get to restricted state is usually either ENDSBS *ALL or ENDSYS, run from the console.

Easiest way to determine when restricted state has been reached (after ENDSBS *ALL or ENDSYS) is to watch the QSYSOPR message queue for a CPF0968 message to appear.

You would normally bring the system out of restricted state by running STRSBS QCTL (or whatever your controlling subsystem is). Starting the controlling subsystem is usually how the system's startup program is run. Or you might instead run PWRDWNSYS with RESTART(*YES) if you want to IPL.