BIND PROCESS

Document Sample
BIND PROCESS Powered By Docstoc
					                           BIND PROCESS

Binding is the process of establishing the relationship between the program
And the DB2 data. This process accomplishes the following:

1) It validates the SQL statements using the DB2 catalog.

2) It verifies that the person binding the program is authorized to perform
   The data accessing operations requested by your program's SQL statements.

3) It selects the access paths needed to access the DB2 data your program
   Wants to process.

4) It builds a control structure (called an APPLICATION PLAN) to allow DB2
   To access the data when the application program is executed.

After you have successfully compiled your application program, it is time, to execute the
bind. For online programs, the member name of the bind JCL,be the 4 digit transaction
name plus the literal PLAN (i.e. TGSKPLAN). For, batch programs, the member name
of the bind JCL should be the program name.TG.T.BIND (TGSKPLAN) & TG.T.BIND
(TG5000) are examples of bind JCL member names.

Review databases and drivers used by the program. The modules needed in the
Bind JCL can be determined as follows.....

In the DB2 verb routines at the end of the program listing:
If a program has a DALLLrrrDS, the bind JCL should have a BSLLLrrr in the
Member list of the bind JCL.

If a program has a DALLLrrrDU, the bind JCL should have a BULLLrrr in the
Member list of the bind JCL.

Bind permissions will be needed. In order to BIND you need DB2 'BINDADD'
Permission and you need permissions on the DB2 tables that are being
Accessed/updated in the plan. In the TEST environment 'BINDADD' permission
Has been granted to PUBLIC and permissions on the DB2 tables has been granted
To RACF groups. You need to be a member of a DB2 RACF group prior to binding
And you need to make sure permissions has been granted to your RACF group on
The tables you are referencing. See.....

DB2 SECURITY STANDARDS


o Permissions are only granted on base view of table. No permissions are
 granted at base table level.

o DB2 permissions will be granted to RACF groups. RACF groups need to be
 Established specifically for DB2 and follow below naming conventions.

 # + Sc + vvvv + f

 Where sc ==> system call letters
     vvvv ==> vcat for db2 sub-system
           DB2T for dsn (test)
           DB2P for dsnp (prod manufacturing)
           D2PF for d2pf (prod finance & admin)
           D2PS for d2ps (prod sales & mkt.)
           D2T2 for d2t2 (test - dba only)
f ==> function
           G for insert, delete, update & select with grant permissions to db2 views and
load, repair & runstats utilities permissions to db2 databases
           U for update, insert, delete & select permissions to db2 views, load, repair &
runstats utility permissions to db2 databases
           S for selects permissions to db2 views


o It does NOT matter what authority someone has within the RACF group. DB2
 Only looks to see that a person is in the group and does not check what
 Authority he has within that group. For information on setting the groups
 Up see.........

A for sysadm permissions
B for dbadm permissions
Example: ED system would set up the following racf groups for test DB2...

 #EDDB2TG - Contains all people who can update, insert, delete and select rows from
ED tables AND grant these permissions to other RACF groups. This group should
Include the project leader(s) for the ED system.

#EDDB2TU - Contains all people who can update, insert, delete and select rows from
 ED tables. This group should include all of the programmers/analysts who work
 With ED tables.

#EDDB2TS - Contains all people who can only select rows from ED tables. This group
 Is for users who need access to ED tables via QMF or SPUFI.

DBA would then run the following SQL statements to grant the
appropriate permissions to ED table ED-MASTER.......

 GRANT INSERT, DELETE, UPDATE, SELECT ON ED.ED_MASTER
 TO #EDDB2TG WITH GRANT AUTHORITY
 GRANT INSERT, DELETE, UPDATE, SELECT ON ED.ED_MASTER
 TO #EDDB2TU
 GRANT SELECT ON ED.ED_MASTER TO #EDDB2TS

Then someone in #EDDB2TG racf group should in turn run the
Following SQL statements to grant permissions to the other
Application areas who use the ED-MASTER table......

 SET CURRENT SQLID = '#EDDB2TG'
 GRANT SELECT ON ED.ED_MASTER TO #TADB2TU
 GRANT INSERT, DELETE, UPDATE, SELECT ON ED.ED_MASTER
 TO #HUDB2TU
 SET CURRENT SQLID = USER
o From now on plans should be bound using the OWNER parameter where
 owner is the RACF group, eg.

 DSN SYSTEM (D2PF)
 BIND PLAN (EDPLANNM) -
   OWNER (#EDD2PFU) -
   MEMBER (BS075011) -
   ACTION (REPLACE) RETAIN -
   VALIDATE (BIND) -
   ISOLATION (CS) -
   FLAG (I)    -
   ACQUIRE (USE) -
   RELEASE (COMMIT) -
   EXPLAIN (YES)

 This enables anyone who is in racf group #EDD2PFU to execute/bind plan
EDPLANNM. If this is a batch plan, you need to add ED to RACF group
 #EDD2PFU so you will be able to execute plan with userid=ED. If this is
 a CICS plan, you still need to grant execute to CICS. You can do that
 as follows....

SET CURRENT SQLID = '#EDD2PFU'
  GRANT EXECUTE ON PLAN EDPLANNM TO CICS
  SET CURRENT SQLID = USER

For more information on binding see...

o In production, DBA will grant permissions on db2 tables to production
 control RACF groups as well as application area RACF groups. This will
 enable production control to bind their production plans. Production
 binds will not use the OWNER parameter in the BIND JCL, instead production
 control will grant execute permissions to RACF group of the application
 area who owns the plan.

o The RACF exit for DB2 is installed in TSO ONLY-- permissions in CICS work
 the same as always.

o The DB2 RACF exit routine filters out all RACF groups that do not
 Have "D2" or "DB" as chars 4-5 in the group name.
BMC Unload

WHAT IS BMC UNLOAD PLUS??,

BMC Software's UNLOAD PLUS utility can be used to unload data from a DB2
Table. The features of this utility include:

- Subset of SQL SELECT syntax for specifying data to unload
- Reduced costs of unloading DB2 data - less CRU consumption (especially for
  Large tablespace scans)
- Ability to unload data from an IMAGE COPY - see DBA if this is required
- Minimal interference with DB2 resources - runs outside of DB2
- Availability of data-type conversions during unloads
- Optionally sorts the unloaded data
- Provides an option to unload Sample rows from a table
- Provides an option to limit the number of rows unloaded from a table
- Optionally produces LOAD control statements or file definition statements
  For use with DB2, FOCUS or SAS

WHEN SHOULD BMC UNLOAD PLUS BE USED?

BMC UNLOAD PLUS should     be used anytime tablespace scans are run against a large table
(minimally 5000 rows). This would replace the IBM unload utility or a READ-EACH
type application program.
The benefits of using BMC UNLOAD PLUS are more substantial on tables substantially
larger than 5000 rows and for data selection where DB2 would not be using an index.

WHAT IS NEEDED BEFORE YOU CAN RUN BMC UNLOAD?,

BMC Unload requires access to the DB2 Table instead of the DB2 View. Special
Requirements follow:

The following permissions will need to be setup by DBA:
  GRANT DISPLAYDB ON DATABASE Dlame TO #scvvvvU;
  GRANT SELECT ON TABLE qualifier. Tlllrrr TO #scvvvvU;
For Seeded Tables:

   If you need to run BMC Unload against a seeded table, you will need to Add selection
criteria to be sure that you are unloading application Data only. Contact your DBA to
determine the format of the selection Criteria and the file layout for the sequential dataset
resulting from the unload.
BMC UNLOAD PLUS - SPECIAL NOTES:,

 NOTE 1: If you are running a BMC UNLOAD PLUS job that requires access to DB2
data which may have been updated in the last few minutes, you will need to run a
QUIESCE utility to be sure the data in the DB2 buffers has been flushed to the VSAM
file. See your DBA for QUIESCE permissions. Sample QUIESCE JCL can be found in
TEST.DB2.SAMPLES(QUIESCE).

 NOTE 2: BMC UNLOAD PLUS automatically calculates and overrides any DCB
information provided. The DCB will always be Fixed Blocked (FB), which does not
agree with GEA standards. You will either need to IEBGENER the file into a file with
VB format, or change any application programs to access data in FB format.
JCL: sample JCL can be copied from TEST.DB2.SAMPLES(BMCUNLD)

   SYNTAX for SYSIN DD *
   UNLOAD SHRLEVEL (CHANGE)
   .
   .
   Options (common options are listed below - none are required)
   .
   .
   SELECT * FROM table WHERE....
   ORDER BY column name, column name....

   ('SELECT *' may be replaced with
    'SELECT column name, column name...')

Common Options:

 PART partition number, partition number...
  - Specifies partition(s) to be unloaded
    Default is all partitions

 CNTLCARDS (DB2LOAD/DB2/FOCUS/SAS)
  - specifies what type of control statements are to
    be written to SYSCNTL - default is DB2LOAD

 ORDER (YES/NO)
 - Allows ordering of the output records by the clustering key
   Default is no

 LIMIT integer
 - Defines the limit on the the number of unloaded records
   Default is 0 (no limit)

 INTERVAL integer
- Allows an unload of sample data (e.g. every 10th row)
  default is 0 (no rows are skipped)

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:4
posted:3/4/2012
language:
pages:5