When upgrading to a new CA Datacom/DB version, all constraints, plans, and views of the previous version must be rebound, but it is not necessary that all constraints, plans, and views be successfully rebound during the upgrade process. You can begin using the new version for the objects rebound and correct remaining rebind problems at a later time. If an object is accessed before it is upgraded, an automatic rebind is attempted.
DBSRFPR can be used to force the rebinding of SQL objects. If desired, you can use this facility after applying solutions that correct errors in bound objects. (The solution's USER ACTION REQUIRED will state that affected objects, usually plans, must be rebound.) Or, for performance reasons, you can use this facility to rebind plans instead of using the CA Datacom Datadictionary Interactive SQL Service Facility plan rebind facility.
Specifying REBIND forces rebinds to be performed even on items that have not been marked invalid by CA Datacom Datadictionary. Valid items whose rebinds are forced in this way are reported as SUCCESSFULLY REBOUND instead of ALREADY REBOUND.
To rebind objects already upgraded or select specific objects to be rebound, add the following parameter to the execution statement in your JCL.
►►─ PARM=' ─┬─────────────────────────────────┬─┬─────────────┬───────────────► └─┬────────────┬─ authid.sqlname ─┘ ├─ REBIND ────┤ └─ objtype= ─┘ └─ CHECKONLY ─┘ ►─┬──────────┬─ ' ───────────────────────────────────────────────────────────►◄ └─ MSG=xy ─┘
Note: Use a single space to separate parameters, but do not use any other spaces (or any commas) in the statement. The parameters can be entered in any order. Use uppercase letters to specify values for the parameters.
(Optional) Identifies the entity-type of the object(s) to be rebound.
With regard to specifying CONSTRAINT= for objtype=, the table name is used rather than a constraint name because all constraints attached to a table are invalidated at the same time, and all constraints need to be rebound before the table is fully useable. Therefore, to save steps and prevent mistakes specify for the value of CONSTRAINT= the table name to which the constraint belongs.
CONSTRAINT= or PLAN= or VIEW=
If objtype= is not specified, constraints, plans, and views are rebound.
(Required, if objtype= is specified.) Enter the authorization ID of object(s) to be rebound, or * (an asterisk) to rebind all AUTHIDs of the specified entity-type(s).
An existing AUTHID or * (the AUTHID may not exceed 18 characters.)
(No default)
(Required, if authid is specified.) Enter the name of a table, view, or plan, or * (an asterisk). Specifying * will cause all entities of the specified AUTHID to be rebound. You can rebind a given entity name under every AUTHID by specifying an asterisk for authid and a specific sqlname, that is, objtype=*.sqlname.
The SQL name of an existing table, view, or plan, or * (the name may not exceed 32 characters).
(No default)
(Optional) Use after upgrade to rebind an object of the new version.
(Optional) When CHECKONLY is specified, CA Datacom/DB reports which plans have bad options and which have bad SQL version/release numbers, but no rebinds are performed. CHECKONLY is ignored if REBIND has been specified (this exclusivity is shown in the previous syntax diagram as a choice between the two).
The plan summary at the end of PARM= execution is different when CHECKONLY is specified in that the number of plans with mismatched versions, bad options, and the total number of plans is reported. If there were plans that had bad options and mismatched versions, those two numbers add to a total that can therefore have a sum greater than the number of TOTAL PLANS displayed. Messages are displayed for plans with bad options and/or mismatched versions.
(Optional) Specify the MSG plan option when rebinding plans. The x refers to precompile-time messages and y refers to messages generated by the Optimizer when the statement is executed. Use S to specify summary, D to specify detail, or N to specify none.
SS, DD, SD, DS, NS, ND, DN, SN, or NN
The default is the MSG plan option for the existing plan in the DDD. If MSG= is specified, it overrides the specification in the existing plan.
Because the following areas are updated, you should back them up before running DBSRFPR:
Order in Which Objects Are Rebound
Objects are rebound in the following order:
A success or failure message in the following formats is written for each object.
CONSTRAINT REBIND ALREADY DONE FOR TABLE xxxxxxxxxxxxxxxxxxxxxxxxxxx CONSTRAINT REBIND SUCCESSFUL FOR TABLE xxxxxxxxxxxxxxxxxxxxxxxxxxx CONSTRAINT REBIND **FAILED** FOR TABLE xxxxxxxxxxxxxxxxxxxxxxxxxxx ... xxxxxxxxxxxxxxxxxxxxxxxxxxxxx NOT REBOUND ... xxxxxxxxxxxxxxxxxxxxxxxxxxxxx SHOULD HAVE BEEN REBOUND
ALREADY REBOUND VIEW xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx REBIND FAILED FOR VIEW xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx SUCCESSFULLY REBOUND VIEW xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
ALREADY REBOUND PLAN xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx REBIND FAILED FOR PLAN xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx SUCCESSFULLY REBOUND PLAN xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
If execution is prematurely ended due to a fatal error, such as table full or index full, the following message is issued:
**** FATAL ERROR - UPGRADE INCOMPLETE ****
A summary report shows, by object type, the number of successful and unsuccessful rebinds, and previously rebound objects:
****** VERSION BOUNDARY UPGRADE REBIND SUMMARY ******** * * ******************** CONSTRAINTS ******************** * * * CONSTRAINED TABLES ALREADY REBOUND : nnnnnnnnn * * CONSTRAINED TABLES SUCCESSFULLY REBOUND : nnnnnnnnn * * CONSTRAINED TABLES WITH REBIND ERRORS : nnnnnnnnn * * TOTAL CONSTRAINED TABLES : nnnnnnnnn * * * ******************** VIEWS ************************* * * * VIEWS ALREADY REBOUND : nnnnnnnnn * * VIEWS REBOUND : nnnnnnnnn * * VIEWS WITH REBIND ERRORS : nnnnnnnnn * * TOTAL VIEWS : nnnnnnnnn * * * ******************** PLANS ************************* * * * PLANS ALREADY REBOUND : nnnnnnnnn * * PLANS REBOUND : nnnnnnnnn * * PLANS WITH REBIND ERRORS : nnnnnnnnn * * TOTAL PLANS : nnnnnnnnn * *******************************************************
Diagnostic information to correct rebind failures is found in the Statistics and Diagnostics Area (PXX). You might need to allocate a larger than normal PXX data set.
The upgrade rebind utility is restartable. Each time it is executed it finds the objects not yet rebound. It issues COMMIT WORK after each successful rebind, so it can be canceled at any time and restarted.
Although the new plans will normally be more efficient, optimization messages are written to the SYSADM.SYSMSG table to help resolve any performance problems.
You can access these messages for review with the following query:
SELECT * FROM SYSADM.SYSMSG WHERE SUBSTR(MSG, 1, 7) = 'REBIND:';
Note: These messages are deleted the first time the plan is rebound after being upgraded.
In most cases these changes will result in better efficiency, but you can force the join order of the previous version by placing the tables in the FROM clause in the join order of the previous version, setting plan option OPT=M, and recompiling the program. This also forces the nested loop join method.
|
Copyright © 2014 CA.
All rights reserved.
|
|