How to define new SQL database areas

Products

• IDMS

Issue/Introduction

This article describes the basic steps for adding new SQL database areas to an IDMS CV.

Environment

Release: All supported releases.

Resolution

For simplicity, this article shows how to add a new SQL database segment with two areas, each of which are related 1-to-1 to their file. Normally, it would be customary to give the files and areas names that might reflect the nature of the data they hold (e.g. CUSTOMER-AREA), but this document assumes simple generic names.

Download the attached .zip file and use the included .txt files as referenced in the steps below.

1. Run a LOOK DMCL SORTED PAGES and examine the output to determine currently unused page ranges to assign to the new area(s).
2. Update the attached SQLWORK.TXT file with the page ranges chosen.
3. Assign appropriate dataset names.
4. Use OCF or an IDMSBCF batch job to add the segment definition to both the SYSTEM and APPLDICT catalogs (i.e. run the utility with the segment source preceded by CONNECT TO SYSTEM; and then CONNECT TO APPLDICT;).
5. Using the file DMCL.TXT as a sample, add the SQLWORK segment to the global dmcl. Be sure to specify a buffer (or let it default to the DMCL's default buffer) whose page size is at least as large as the page size of the areas.
6. Compile and link the CV's DMCL.
7. Using ALLOCATE.TXT as a sample, allocate the database files. Be sure to assign the number of blocks in accordance with the number of pages chosen for each file.
8. Using a local-mode IDMSBCF batch job, format the new files. See the file FORMAT.TXT for sample SYSIPT. 
9. Cycle the CV - the new areas should now be in the CV. Use LOOK DMCL to confirm.

After the above steps are complete, SQL tables defined in the APPLDICT catalog will be able to be allocated to areas SQLWORK.SQL-AREA-1 or SQLWORK.SQL-AREA-2.

Additional Information

Physical Database DDL Statements
Creating a Table