AU2004202616B2 - Method for Maintaining Exception Tables for a Check Utility - Google Patents

Description

16 Jun 2004 10:53 Smoorenburg Rttorneas +613 9712 0159 p.4 METHOD FOR MAINTAINING EXCEPTION TABLES FOR A CHECK UTILITY TfIE.L OP TH_ TINVE'fIOUN The present invention relates to database management systems, and particularly to a method for exception tables for a check utility in 'a database management system.

BIACGROND INPDRMATION A well akown database software program is DATABASE 2 (DB2) database software distributed by IBM Corporation. As is known in the art, DB2 operates as a subsystem in a computer system operating under the IBM MVS operating system software. In a DB2 environment, user data resides in DB2 tables which are in tablespaces. A tablespace is, for example, a portion of storage space in a direct access storage device (DASD) such as a disk drive. For exemplary purposes, illustrated below is an orderentry table that would be stored in a tableapace. The orderentry table contains columns: customer.number; productcode; order.pumber; buyer-name; and shiptozip.

Orderntry Table custoer.snuber producetcode ordertuaber

B

aes nIame shiptozip 1111111111 0010 1234500001 John Doe 60606 1111111111 0040 1234500002 Jane Doe 70707 3333333333 0020 1234500003 Bill smith 90909 2222222222 0030 1234500004 Fred Smith 80808 While the above OrderEntry table shows four rows, the table could have millions of rows for all the orders of a company, for example 4 million rows. The order_entry table also has, for example, three index keys and two foreign keys. An index COMS ID No: SBMI-00791549 Received by IP Australia: Time 11:05 Date 2004-06-16 16 Jun 2004 10:54 Smoorenburg: Attarnems +613 9712 0159 key is an identifier for a particular row of a table while a foreign key also identifies a row but is also used for referential integrity as described below. For example, in the orderentry table, one index key could be based on OrderNuwber, another index key based on buyername and a third index key based on ship_to_zip. As is known in the art, an index key for a particular table indicates a row identification (RID) and a selected value for the row the index key value).

The index key can be used to generate an index for the table which facilitates subsequent searches for particular data in the table- For example, the Order_Entry table would have three indexes one for each index key), each index being stored in an indexspace. Similar to a tablespace, an indexspace is, for example, a designated portion of a DASD.

Thus, if a user was looking for rows that contain a particular buyer name in the OrderEntry table, the database management system could query the buyer index for the table to identify all occurrences of the buyer name without reading the entire table to locate the rows.

DB2 administrators analyze performance characteristics for application programs that access a database table in an attempt to find the optimum index structure for fast access to the database table. The values to be used as an index must be carefully selected because each index-results in overhead for the database system. For example, each transaction in a database tablee, such as an add or delete, requires that each index for the table also be updated. Thus, it is desirable that the number of indexes for a table be minimized to enhance the perfcman ibc of application programs. The values to be used as an index for a database table are selected based on, for example, data accessed most frequently by users of the table, generally on-line transaction users. Index keys 2 COMS ID No: SBMI-00791549 Received by IP Australia: Time 11:05 Date 2004-06-16 16 Jun 2004 10:54 Smoorenbure Rttorneus +613 9712 0159 p.G generally are not based on foreign keys, as foreign keys are used primarily for validation purposes constraint enforcement).

?a is known in the art. each table in a database may be either a parent table, a child table or both. A child table is related to a parent table via the foreign key value or values contained in columns of the child table. For example, a foreign key value can appear multiple times in a child table multiple rows in a child table can have the same foreign key, such as the customer_number and productcode entries in the orderentry table) but each foreign key must be associated with a unique key in a parent table of the child table.

Referential integrity ensures that every foreign key value is valid has a corresponding primary key in a parent table). Thus, referential integrity (RI) means that a value in the column of a row in the table is valid when this value also exists in an index of another table. A row should not be in a table if it violates a constraint. As the order_entry table illustrated above has two foreign keys, it has a RI constraint on custonernumber and product_code. As is known in the art, when a user of a DB2 database management system creates a table, the user also defines the constraints for the table the user can define the relational integrity criteria). Illustrated below are an exemplary product table and an exemplary customer table the parent tables for the foreign keys in the order_entry table).

Product Table product-sode product-description retailprice 00010 laptop pC 11000.00 COMS ID No: SBMI-00791549 Received by IP Australia: Time 11:05 Date 2004-06-16 16 Jun 2004 10:54 Smoorenburg Attor-netjs +613 9712 0159 p.7 productcode productdescription retail price 00020 desktop pc 1100.00 00030 office pc 1200.00 00040 lan pc 3500.00 00050 home pc 999.99 The product table show five rows, although the table could have thousands of rows for all of the different products of a company. The product table has, for example, an index based on the column productcode, which values are illustrated in ascending order. The values in the column productcode are each unique since there is only one product code assigned to each product and thus in this table, a product code would not be included more than once. Accordingly, an index for the product table would include the key value the stored value in the productcode column) and a RID. The product table index would reside in a DB2 indexspace.

The customer table illustrated below shows four rows, although this table could also have thousands of rows for all of the customers of a company. The customer table has, for example, an index based on the column customernumber, which values are illustrated in ascending order. The values in the column customernumber are each unique since there is only one customer number assigned to each customer name and thus a customer nmb er would not be included in this table more than once. Accordingly, an index for the customer table would include the key value the value of the column customer.number) and a RID. The customer index would also reside in a DB2 indexspace.

4 COMS ID No: SBMI-00791549 Received by IP Australia: Time 11:05 Date 2004-06-16 IS Jun 2004 10:54 Smoorenburg ttornebs +613 9712 0159 p.8 Customer Table custoaer .nudzer buyerJiame custameraddress 111111111 John Doe State A 2222 222 Fred Smith State B 3333333333 Bill Smith State C 4444444444 Steve Jones State D As shown by the above tables, all of the rows in the Order.Entry table are valid there are no referential integrity constraint violations) because the foreign key values in the column product.code of the OrderEntry table also exist in the product table and the values in the column customernumber of the Order_Entry table also exist in the customer table.

Conventional database management systems, such as DB2. provide the user with the ability to identify specific conditions that a row uist meet before it can be added to a table. These conditions are referred to as "constraints" because they constrain the values that a row may include. Constraints include. for example, check constraints and referential integrity constraints. Check constraints include, for example, qualifying criteria for a particular value, such as a zip code value the shiptozip value in the Order_Entry table) being in the range of 00000 to 99999. As discussed above, referential integrity constraints ensure that a value in a row of a table is valid when the value also exists in an index of another table.

Constraint enforcement can be performed prior to loading of data into a database table or after data has already been loaded into a database table. When constraint enforcement is performed after loading data into a database table, for example as part of a recovery operation following a hardware COMS ID No: SBMI-00791549 Received by IP Australia: Time 11:05 Date 2004-06-16 16 Jun 2004 10:55 Smoorenburg Attrnejs +613 9712 0159 p.9 of software failure, the constraint enforcement is generally performed by a CHECK utility, such as CHECK DATA by IBM Corp., CHECK PLUS by eBC Software and FASTCHECK by Platinum technology, inc.

Conventional CHECK utilities ensure that data in the table do not violate any constraints that have been established for the table. Constraints can be established at the time the table is generated. For example, constraints can be defined when the table is originally created in the database system and are stored in the DB2 catalog, which can be subsequently queried by a CHECK utility to identify the constraint information.

To perform constraint enforcement, a Conventional CHECK utility would, for example, be initialized and identify any applicable constraints for the table to be checked by reading the DB2 catalog, as is known in the art. The CHECK utility would, for example, then read each row of the database table and check for check constraint violations and/or referential integrity canstraint violations.

As is known in the art, an exception table is generated prior to each time a CHECK utility operates upon a table a new exception table is generated or a prior exception table replaced each time constraint enforcement is performed). For example, when a user creates a Job stream to execute a CHECK utility, a step of the job stream includes creating a new exception table. The exception table is, for example, a mirror image of the database table except that the exception table only contains the rows including a constraint violation.

For example, each time a CHECK utility identifies a constraint violation, the CHECK utility copies the entire row into the exception table. An exemplary SQL statement to copy rows in error into an exception table is as follows.

6 COMS ID No: SBMI-00791549 Received by IP Australia: Time 11:05 Date 2004-06-16 1G Jun 2004 10:55 Smoorenburg Attorneds +613 9712 0159 ZMSURT aINT PDL m. ZwDOcopp VAZUM I' ALE' ,'2004-10-04' '07.SS.34" ,o .3332P ,x00002021 ,CtmUBUM TMESlTA) I As shown by the above code, a row containing a constraint violation in database table PDLNR.TDOCDPP will be copied into exception table PDLNR.EXTDOCDPP4.

Prior to the CHECK utility utilizing an exception table, however, the exception table must be created. Further, if the CHECK utility has previously operated upon a table, the previously created exception table must be deleted and a new exception table created. An example of execution of a prior art CHECK utility is as follows regarding creation and deletion of exception tables. For example, assume a typical user application system having three parent tables and thirty one dependent tables (other combinations of parent and dependent tables are possible). When the customer executes a conventional CHECK utility for the dependent tables, an exception table is needed for each dependent table. As described above and known in the art, each exception table is, for example, a work table used to contain rows that the CHECK utility identifies as violating a constraint. When checking a dependent table tableapace for a referential integrity constraint violation, the user of a conventional CHECK utility needs to perform the. following steps.

For example, for each dependent table, the user must code and execute the following exemplary SQL statements.

DROP TABLESPACE DBZSXP.TSISIPOIr CREATE TABLESPACE TSIBIP01 IN DBI6P USING STOGROUP SGISIP PRIOFT 52 7 COMS ID No: SBMI-00791549 Received by IP Australia: Time 11:05 Dat4 2004-06-16 16 Jun 2004 10:55 Smoorenburg Rttorne!ds 16 un 0041055 mooenbrgRttrnejs +613 8712 0159 P.11 swcqrr 26 ERASE NO FREEPAGE 0 LOCKSZ3 am! LOCUKAX nerdZ CCSID nBcvic £R3A73 TABLE PD? hZP.TBHOEDCT LIKE PDUTmAZ.TSNORDC? rM DBEsZP.TSZSPOZI ALTER TABLE PDM gIP.TSISIPO1 AMO RIDCHAR144) ALTER TAB3LE PDZSIP.TMNRDT AMO TIKE TINEBTAMP NOT NULL WITH DEFAULT The above code describes, for example. the steps of dropping an existing exception table TSISIPOI) and creating a new exception table TSZ&IP0l) for each dependent table to be checked. in addition, the 'Code illustrates exemplary alterations weeded by the CHECK utility for the newly created exception tables add a new colum for row identification and anew column for a timestaip).

As the above example his thirty one dependent tablespaces, the above code must be writt~rt and executed thirty one times once for each dependent tablespace). In addition, the user of the CHECK utility also has to cods a control statement to run the CHECK utility, the control statembent naming all of the dependent tableupaces'to be checked as well as identifying all.,the excepion tables associtied with the dependent tables.

ExeVplar SQL stateaents that Would he written by a user for thisf purpose awe shown be ow.

CHECKRMaTA, TAWSSACE U7TZNLAN.SCROJWC *TADLISPACE, 3-rnLAN.8ccMDI T AsL3SnCE 3'rnuaND-cm~rNF TAZMPAcE JIXNLAM.SCHRKVLI dS TASI3SWM2 fTINXLAN. SCHENRAC .TABWhPfl; flnu&An. sacoanj *TASLESPAM JTZNLAND. SCTCK *TARLNSRAcE rflULAN. BCNSIWED COMS ID No: SBMI-00791549 Received by IP Australia: Time (i-tm) 11:05 Datd(V-M-d) 2004-06-16 16 Jun 2004 10:513 Smoorenburg Attornems 16 un 00410:6 SsorenurgAttrnejs +613 9712 0159 P.12 *TRDL3SPhCE 3'?DILAW.

SCPSCE

*aSUPAC J~jpLA.SQONFCHI 'AXLusFlf aTZNLW.

SQOWIN

TABLESPACE flIXND.

SQOKIWVU

TABLESPACE fTLUZAD. QWOflWL *TAS'ESFACB .flzNLRND

SQOMFOYP

nm.SPAC I tNgM SQQS7CH AgLdPACE 37133M0.QOflW' *xm~ flLTflE ZIAN.SQwtFMtR *TASLSSPZ jTZDILM.

SCKEZTUR

*TA3L3S1ACZ JtfMhD SCHOIDW *TAILESp3CE 3nuzXWD.

SCHORULD

*TASLEPACS flZNLAND. SCHRZFUL *TARJISZACZ rrZVLAifl. SCNRETYfP *TASLTSACE .YTINTAIIV. CPEPICS *TASIZSACE a'rXNLMV. SOC=h *TASLESPACU JTXuLAJW.SCKCRCKN 21DFOR EXCEPTIONtIZN Pl EXCEPTION IN P FOR EXCEPTION MR P rPC* ncEtn OiN 11 FOR EXCEPTION INF Rm N3CEwfOi N N FOR EXCEPTION ZN FOR nc=flOUK IN P: FOR EXCEPTION

INF

FOR EXCEPTION

INP

FOR EXC1EPTIOH N ni FOR EXCEPTION XN FOR EXCEPTION

SNP

FRc XCcEflION mwN Fro nXCSPwIOP in FOR EXCEPTIou inP FOR EXCEPTIOhN IN P FOR EXCEPTION

INK

FOR OCEPTZON IN Frc nl~cerltIN Xw FRM SXCEPTZCI4 in FOR EXCEPTION SKI rot azesPyzx Iw FOR EXCEPTIONI IN I roM neuPTIC N i FOR EXCEPTION 1111 FOR .XCSPTION

IV

FOR E=j3PTIWK IN FRm EXC3PTICIU

IN

FOR NZCCPTZO N I SO FOR zxcuwzow

IN

rI'L)3.TUHORDCT USE VDZSZP TSITORD :3tnLO3 .TSITNOI USE Ff151? .TEHZTWDI DUIWS .TBUMDVI UsE PDIBIP ?ENHDNF DUTbO3 .TSIEEFL2 USE PDISIFP.TBKRRFL3 DOTLO3 .TSN4CIIL 1TSZ PDjflP.ThHCflIL DUTLOS.TWSI4DLOO USE poZSZP.TDMSILDG- DUTLO3 .TDNFACFW USE PFOSSX. TWIFACFW DUrLO3.TSNHSAC lugs PD! SIP. TBHEHRAC DqYLO.03THCOAfl 11018 wvzSZP.TSHCOATZ WILO 3 .753C1!OC UjSE P01811 .TSHCTOCN DUrLO3. .TDSS2CX USE PVISIP.THSSTCX% DUTLO) .TUNIROR 0SE PDIS P .TDHSRORD DUTLOP .TBVPRCIUS 153PDISZP.TBPEFRCE DVfTfLO. TOFEND 1St POISIP. TQCFGHD Dmfl.3 TQKF CH.1 USC P0151 I. TQONFCKZ 'DtnLO3 .TQOKFCU USE PDIII P. TQOKFCNT tflLO ToNFoYR USE P01912. TQCWOVR1 OUTLOS TOKFPOV USE P01512 .TQONFOVU IDU1L3.TOONFNPL USE PDISZP-?OOHFPL WTLO TQCIIovp UjSE PD? SIP. TQOHOVP wDUTOS .TQOmFCKE use 101511. TONFCHE rnULO3 TQOKFLUW USE 2021?. TQONPUN? '013TW3.TOO PRQ USE vDZSIP.TONlFTRO PDUTL3 .TEEITNR USE 10151?. TBEZTNR POUL).TBNORDVW USE 10151?. TDNORDFW wYLOS 3. TSRlORHLD USE POISE?. TENRELD PMtITL3 suw~fL US: I10151?. nBulZF PDU*LOS .SIRNIW UBE PD!! IP.TDNR1TYV PDUTLO3 .IEPZCS USE PCXZIP.TBIPEPZCS p=ULO TSIECVL USE IDISIP .T3HERCYL PCUMO3 IDICACRWV SE POW ZP.-TUXCACKW aLS shown by- the akbovo control statement, each dependent tablespace is identified -TABLESPACE

JTINJAW.SCORDCT"

identifies dependent table SCHORO)CT owned by database JTIILAD) After the thirty one dependent tables are identified, the exception table to be used f or each dependent table Is identified 'fFOR EXCEPTIOW ISN PblUTL03.TBHORDCT USE PDZSIP -TUHOFDCTW identifies exception table TBHORPCT to, be 9 COMS ID No: SBMI-00791549 Received by IP Australia: Time 11:05 Date 2004-06-16 20/08 2008 WED 15:11 FAX Smoorenburg PinI 44- IP AUSTRALIA Z004/019 00 Cused for independent table TBHORDCT).

As indicated by the above exemplary code that must be written and executed for operation of conventional CHECK utilities to perform constraint enforcement, substantial effort must be expended by a user to, for example, drop existing exception table tablespaces created from prior operation of the CHECK O utility and to create a new exception table tablespaces.

O As databases frequently have large numbers of dependent tables, the user must periodically perform referential integrity checks on these tables and all dependent tables. This process can be long and convoluted involving many steps and substantial amounts of hand coded SQL commands to identify the Stables to check, create exception tables and specify the exception tables to the referential integrity CHECK utility. This process is not only time consuming, but also is prone to error. Therefore, it is desirable to simplify the tasks associated with dependent table referential integrity checking in an automated fashion.

SUMMARY OF THE INVENTION In one aspect, the present invention provides a method of maintaining exception tables, including the steps of: generating a control statement including predetermined exception table parameters, the control statement identifying one or more parameters needed to perform constraint enforcement on selected tablespaces, and the exception table parameters including at least whether to drop or reuse a previously existing exception table; receiving the control statement in a CHECK utility; automatically generating a SQL statement for each of the predetermined exception table parameters; and executing each SQL statement, wherein one of generation and deletion of an exception table is performed as a function of executing at least one SQL statement, the exception table having an image of one or more database rows that contain a constraint violation.

In another aspect, the present invention provides a method for maintaining exception tables in a database system, including the steps of: receiving a control statement, the control statement including at least one predetermined exception table parameter, the control statement identifying one or COMS ID No: ARCS-202857 Received by IP Australia: Time 15:12 Date 2008-08-20 20/08 2008 WED 15:11 FAX Smoorenburg Pinl 44 IP AUSTRALIA R005/019 00 C more parameters needed to perform constraint enforcement on selected tablespaces, and the at least one predetermined exception table parameter including whether to drop or reuse a previously existing exception table, an exception table having an image of one or more database rows that contain a constraint violation; IDparsing the control statement; IDautomatically generating a SQL statement as a function of the parsed control statement for the at least one exception table parameter; and executing the SQL statement.

In a further aspect, the present invention provides a method of maintaining c exception tables, including: generating a control statement including one or more predetermined exception table keywords and one or more selected tablespaces, the control statement identifying one or more parameters needed to perform constraint enforcement on selected tablespaces, and the one or more predetermined exception table keywords including whether to drop or reuse a previously existing exception table, an exception table having an image of one or more database rows that contain a constraint violation; automatically generating a SQL statement for each of the predetermined exception table keywords; executing a SQL statement to operate on the one or more selected tablespaces and to operate on dependent tables of the one or more selected tablespaces; wherein one of generation and deletion of an exception table is performed as a function of executing at least one SQL statement.

In still a further aspect, the present invention provides a method for maintaining exception tables, including: generating a control statement including one or more predetermined exception table parameters for processing by a check utility, the control statement providing instructions to the check utility for creating one or more exception tables having an image of one or more database rows that contain a constraint violation, the control statement identifying one or more parameters needed to perform constraint enforcement on selected tablespaces, and the exception table COMS ID No: ARCS-202857 Received by IP Australia: Time 15:12 Date 2008-08-20 20/08 2008 WED 15:11 FAX Smoorenburg Pini IP AUSTRALIA Q006/019 00 c" parameters including at least whether to drop or reuse a previously existing exception table; automatically generating SQL statement for each of the predetermined exception table parameters; and executing each SQL statement.

IND According to an embodiment of the present invention, a process identifies I\ the tablespaces to be checked by a CHECK utility so that the tablespaces and 0associated exception table tablespaces are automatically identified to the CHECK utility, thereby eliminating the need for substantial handcoding or SQL statement required by conventional CHECK utility operation. According to an exemplary embodiment of the present invention, a control statement including predetermined key words is provided to a CHECK utility, the CHECK utility parsing the control statement to drop existing exception table tablespaces and create new exception table tablespaces.

Other aspects and preferred aspects are disclosed in the specification and/or defined in the appended claims, forming a part of the description of the invention.

COMS ID No: ARCS-202857 Received by IP Australia: Time 15:12 Date 2008-08-20 18 Jun 2004 10:57 Smoorenburg Rttorneys +613 9712 0159 HRIEP DESfCRIT OT OP THE DRAWINGS Figure 1 illustrates an exemplary flowchart for maintaining exception tables for a CHECK utility according to an embodiment of the present invention.

Figure 2 is illustrates another exemplary flowchart for maintaining exception tables for a CHECK utility according to an embodiment of the present invention.

DElTATLDf nSCRTPSTON OP 'T1 TrNVENTION Figure 1 illustrates an exemplary flowchart for maintaining exception tables for a CHECK utility according to an embodiment of the present invention. As illustrated in Figure 1, in step 1010, a control statement is read identifying the parameters needed to perform constraint enforcement on the desired tablespaces. In step 1020, the control statement is processed by, for example, a conventional CHECK utility modified in accordance with an embodiment of the present invention. In step 1030, the SQL statements needed to drop or create exception tables are generated. In step 1040, the SQL statements generated in step 1030 are executed.

Set forth below is an exemplary SQL control statement according to an embodiment of the present invention. For example, this control statement could be generated by a user of a CHECK utility in step 1010 illustrated in Figure 1. The control statement would then be provided to a conventional CHECK utility for processing in accordance with an embodiment of the present invention.

CBCK DATA TraLESPACE TIIAMD. SCtOMWCT TABLESPACB JTLUAID.SCBITDi Em ESPAC TABLsE8sPACE TrnLmD.SCU TABLSPACE JTIXMA.MD.CHCOiJa.

TABLSPACB JTsmU.O.8CMRUDG STABLESPACE JTIiAWD.SCHFPAC r STABLBSWPCE JTxtLAuD. SCNHAC 11 COMS ID No: SBMI-00791549 Received by IP Australia: Time 11:05 Date 2004-06-16 16 Jun 2004 10:57 SmoorenburC Attornetis 16 un 00410:7 Soornbug Atore~s +613 9712 0159 P.16 12 TAULZSIAZ 3flU3JW. AUZSPACN CflJUb UOIIITC ADLESPAZ 3'flNLAND.SOISRCU s .TAflEaSPACE JTZULRW SOePMO TM.SCE 3flVL&KD. SON CE *TARLZSPM2 OYZWLAMW.5000CW *TA3LKSPAZ J7ZLAND. SQOSWC3 nmaSPACU onMAlI -soowPova TIWLISPACI 3TIULAMD.SOWWOYU ?ArLZSACZ 3TZND. SOPUII 4 STAWZSonC3 JTMANMD. SOOKPOV *TflLtSPAC OflfIAW SQOKVOH *TRSKspACx ainnmP. sonr 'asZflflCR rZNLMED. SQOfRQM nBLSpAC 3fl1a3. aCZfRlf 2D flDBLEPACN .flDCUND. SOKOEDIW ABLSSPAC lflMIW. SCNECB.

*TANLESPACB 3nIUAUD CHRZVRL nSLUSPACS JflNLAND. ICKIUC USINST"B =XI TAULSIPACE 221811 STIOOROUP IC=81P P31027 52 suowrr 24 0S55KL 101811P DRop YES AM indicated in the above exemlary SQL control. statement, the control statemeunt contains, f ollowing the term USING, several key words that ar* procesused by a conventional CHECK iuLilitY in accordance with an embodimnent of the present invention.

The forznt of the control statemsnt is a raster of dmsign choice, provided that the forMat can be processed parsed) by the check utility. Prior to the key words, however, the names of the dependent tablespaces to be checked are :provided by the user, fromt which the dependent tables to be checked can fre determiined.' For exanple. the CHECK utility can identify the name of dependent table in each dependent tablegpaCe by. f or example, reading the D32 catalog of the database, as is know in the art.- A description of how to .read fth DB2 catalogl is coalaied in US patent No. 6,295,539 entitled DYNAMIC DETERMINATION OF OPTMAL PROCESS FOR ENFORCING CONSTRAITS ssigned to the COMS ID No: SBMI-00791 649 Received by IP Austrai: Time 11:05 Date 2004-06-16 16 Jun 2004 10:58 Smoorenburg Attorneys +613 9712 0159 p.17 assignee of the present application and which is hereby expressly incorporated by reference.

In another embodiment of the present invention, instead of S providing the names of all the dependent tablespaces to be checked, the names of the parent tablespaces can be provided in the control statements. With this information, the DB2 catalog could be read to identify the associated dependent tablespaces, thus eliminating the need for the user to even manually identify the dependent tablespaces. For example, the control statement identifying only the parent tablespace can have any format or syntax provided that the format or syntax can be interpreted and processed by the appropriate entity, such as a CHECK utility or other DB2 utility that can access a DB2 catalog.

An exemplary SQL control statement according to an embodiment of the present invention includes key words for predetermined exception table parameters. For example, one key word is the name of the database in which the tablespaces will be created the database name for the logical grouping of newlycreated tablespaces). In this example, the name of the database is DBISIP. Another keyword is the exception tablespace name to be created, for example TSZSIP (which is in database DBISIP). If multiple exception tablespaces are needed, the method according to an embodiment of the present invention can create them, for example consecutively numbering tablespaces using the amea tablespace name TSISIP01, TSISIP02, etc.). The Ney words also include a storage group identifier as is known in the art where the exception tablespace will reside). In this example the STOGROUP is named SGISIP.

The key words according to an embodiment of the present invention also include, for example, a primary quantity of 13 COMS ID No: SBMI-00791549 Received by IP Australia: Time 11:05 Date 2004-06-16 16 Jun 2004 10:59 Smoorenbur-g Rttanne2$s +613 9712 0159 p.18 DASD and secondary quantity of DASD to be used within the storage group the gages of DASD to be allocated by DB2 for the exception tablespace). The primary and secondary quantities of DASD can, for example, be specified by the user, as is known in the art, for example based on an estimate of the number of errors that will be identified by the CHECK utility). In this example, 52 pages of DASD are allocated for the storage group with another 26 pages available if necessary.

As illustrated in the control statement, the key words may also identify an owner of the exception tables to be created, in this case PDISIP. This owner will be used, for example, in the CREATE TABLE SQL statement generated by the CHECK utility in response to the control statement according to an embodiment of the present invention. In addition, the key.

words include a drop statement to indicate whether existing exception tables are to be dropped before a new exception table is created or if existing exception tables are to be reused. According to an exemplary .ebodiment of the present invention, newly created exception tables are assigned an owner provided by a user and have the same name as the corresponding dependent table being checked which has a different owner name).

An exemplary process for a conventional CHECK utility to generate and drop exception tables in accordance with an exemplary embodiment of the present invention is set forth as psuedo code below and illustrated in Figure 2.

PAisE cofkOrrDar sarasert IF DROP YES 5 DROP T=8 TABLESPACE

BNDZP

CRlMfs TABL3SraCEB .CREAT TABLE ALTER TABLE TWIC 14 COMS ID No: SBMI-00791549 Received by IP Australia: Titne 11:05 Date 2004-06-16 16 Jun 2004 11:00 Smoorenburg Rttornes +613 9712 0159 p.19 SET FLAG EXCEPTZON.TABLE SET FLAG DROPtIrMUPT

ELSE

IP xRBWOD a FOR EXCEPTzOX IN SET FLAG EXCEPTIONTABLE

ENDP

PERFORM CHECK DATA PROCESS IZ NOVIOLATIONS IF DROPIF..cTY 1D DROP TAWLEBPACES

ENDIP

Figure 2 illustrates another exemplary flowchart for maintaining exception tables for a CHECK utility according to an embodiment of the present invention. In step 2010, a control statement is received by a conventional CHECK utility.

Por example, according to an exemplary embodiment of the present invention, the control statement would include predetermined exception table parameters, as described above, for parsing by the CHECK utility. In step 2020, the received control statement is parsed and it is determined if a the DROP parameter is set to, for example, yes or no. If the value is no, the existing exception tablespace from a prior checking operation will be used for the current checking operation. If the drop value is yes, then in step 2030 the existing exception tablespace will be dropped.

Following step 2030, a CREATE TABLESPACE SQL statement is built in step 2040 by the CHECK utility using, for example, the tablespace name obtained from the parsed control statement a data structure is built). In step 2050, the exception tablespace is generated, for example by executing the CiEAtZ TABLESPACE SQL statement, a portion of DASD is allocated for thi exception tablespaces) In step 2060, it is detenmined if the exception tablespace previously existed.

For example, DB2 indicates via a SQL code) if a new exception tablespace was created if the execution of the SQL statement was successful) or i it could not be created, for example, because the tablespace already existed.

If the exception tablespace previously existed, the process COMS ID No: SBMI-00791549 Received by IP Australia: Time 11:05 Date 2004-06-16 16 Jun 2004 11:00 Smoornburg Rttornems +613 9712 0159 continues at step 2090, described below. It the exception tablespace did not previously exist, then the process continues at step 2070. In step 2070, a CREATE TABLE SQL statement is built and executed to create the exception table.

In step 2080, any required ALTER SQL statements are built and executed, for example to add row identification or a time stamp to the exception table.

In step 2090, it is determined if there is another dependent table to check. If there are no more tables to check, the process ends at step 2100. If there is another table to check, the process returns to step 2020 until all identified tables'have been checked. In contrast to.coventional CHECK utility handling of exception tables, which require that all exception tables be identified by the user of the CHECK utility, according to the present invention the dependent tables are automatically identified and the corresponding exception tables generated, thus avoiding potential errors or omissions in identifying all required exception tables.

Accordingly, the above-identified process can be performed by a conventional CHECK utility the method can be provided as a software algorithm in the CHECK utility software) to create and delete exception tables according to an embodiment of the present invention. For example, using the method of the present invention, the inclusion of the three parent tables in the control statement along with the specified key words would result in the automatic generation and execution of SQL statements needed to generate the thirty one exception table tablespaces required for constraint enforcement. Thus, according to anembodiment of the present invention, less work is performed by a user of a CHECK utility to generate the exception tables required for referential integrity constraint enforcement than would be required with conventional approached to constraint checking.

16 COMS ID No: SBMI-00791549 Received by IP Australia: Time 11:05 Date 2004-06-16 20/08 2008 WED 15:11 FAX Smoorenburg Pini IP AUSTRALIA @007/019 00 16a CN "Comprises/comprising" when used in this specification is taken to specify Sthe presence of stated features, integers, steps or components but does not preclude the presence or addition of one or more other features, integers, steps, Scomponents or groups thereof, COMS ID No: ARCS-202857 Received by IP Australia: Time 15:12 Date 2008-08-20

Claims (7)

1. A method of maintaining exception tables, including the steps of: generating a control statement including predetermined exception table parameters, the control statement identifying one or more parameters needed to INO perform constraint enforcement on selected tablespaces, and the exception table O parameters including at least whether to drop or reuse a previously existing exception table; receiving the control statement in a CHECK utility; automatically generating a SQL statement for each of the predetermined Cexception table parameters; and executing each SQL statement, wherein one of generation and deletion of an exception table is performed as a function of executing at least one SQL statement, the exception table having an image of one or more database rows that contain a constraint violation,

2. A method for maintaining exception tables in a database system, including the steps of: receiving a control statement, the control statement including at least one predetermined exception table parameter, the control statement identifying one or more parameters needed to perform constraint enforcement on selected tablespaces, and the at least one predetermined exception table parameter including whether to drop or reuse a previously existing exception table, an exception table having an image of one or more database rows that contain a constraint violation; parsing the control statement; automatically generating a SQL statement as a function of the parsed control statement for the at least one exception table parameter; and executing the SQL statement. COMS ID No: ARCS-202857 Received by IP Australia: Time 15:12 Date 2008-08-20 20/08 2008 WED 15:11 FAX Smoorenburg Pinl 4 IP AUSTRALIA Z009/019 00 18 N 3. The method according to claim 2, wherein the at least one predetermined Sexception table parameter includes one of a database name, an exception tablespace name, a storage group name, a primary quantity value, a secondary quantity value, an owner name and a drop value.

4. The method according to claim 3, wherein the control statement includes a \plurality of exception table parameters. (NO The method according to clam 2, wherein the step of parsing the control statement is performed by a conventional CHECK utility.

6. The method according to claim 2, wherein the step of generating the SQL statement includes generating a SQL statement for a plurality of exception table parameters.

7. A method of maintaining exception tables, including: generating a control statement including one or more predetermined exception table keywords and one or more selected tablespaces, the control statement identifying one or more parameters needed to perform constraint enforcement on selected tablespaces, and the one or more predetermined exception table keywords including whether to drop or reuse a previously existing exception table, an exception table having an image of one or more database rows that contain a constraint violation; automatically generating a SQL statement for each of the predetermined exception table keywords; executing a SQL statement to operate on the one or more selected tablespaces and to operate on dependent tables of the one or more selected tablespaces; wherein one of generation and deletion of an exception table is performed as a function of executing at least one SQL statement. COMS ID No: ARCS-202857 Received by IP Australia: Time 15:12 Date 2008-08-20 20/08 2008 WED 15:12 FAX Smoorenburg Pini IP AUSTRALIA 1010/019 00 0 19 C 8. A method for maintaining exception tables, including: generating a control statement including one or more predetermined exception table parameters for processing by a check utility, the control statement Sproviding instructions to the check utility for creating one or more exception tables having an image of one or more database rows that contain a constraint violation, I the control statement identifying one or more parameters needed to perform \constraint enforcement on selected tablespaces, and the exception table parameters including at least whether to drop or reuse a previously existing exception table; automatically generating SQL statement for each of the predetermined C exception table parameters; and executing each SQL statement.

9. A method of maintaining exception tables as claimed in claim 8, wherein the generating a SQL statement includes: generating automatically form the control statement a SQL statement for each of the predetermined exception table parameters, the SQL statement further including one of generation and deletion of an exception table.

10. A method substantially as herein described with reference to at least one of the accompanying drawings. COMS ID No: ARCS-202857 Received by IP Australia: Time 15:12 Date 2008-08-20