Creating and Editing DataBases
Go Up to Creating and Editing Database Dependent Objects
Databases are Db2 structures such as collections of tables with associated indexes, as well as the tablespaces and index spaces that contain them. Using databases for administration, you can limit access to the data in the database in one operation, or give authorization to access the data as a single unit.
When you create a database, ER/Studio Data Architect performs the following tasks:
- Sets up system catalog tables required by the database.
- Allocates the database recovery log.
- Creates the database configuration file and sets default values.
- Binds the database utilities to the database.
Before creating the database, ensure you have considered the contents, layout, potential growth, and usage.
The following database platforms support database creation:
- IBM Db2 for z/OS 5.x+
Note: The Database Wizard and Database Editor share the same options, except for Attachment Bindings options which are present only in the editor.
Add a Database
To add a database
- In the Data Model Explorer, expand a physical model, right-click the Databases node, and then click New Database.
- Complete the Database Wizard, and then click Finish to create the database.
- Once you create the auxiliary table, you can edit it by right-clicking the auxiliary table you want to change, and then clicking Edit Auxiliary Table.
Name & Type page/tab
Name the database and specify the type. The database cannot already exist on the current server. The name must not start with DSNDB, unless it is a workfile database and must not start with DSN followed by five digits. Select WorkFile Database (AS WORKFILE) if the database operates in a data sharing environment and you are creating a database used for purposes such as managing the data of temporary tables, storing intermediate SQL results, and creating global and temporary tables. If you choose to create a WorkFile Database, you must also specify the member or Db2 subsystem that can use the workfile. Only one member of the data sharing group can use this workfile.
Option page/tab
To make the database easier to manager, keep work files in a separate buffer pool.
- Tablespace and index buffer pools. (BUFFERPOOL and INDEXBP). Identifies the buffer pools that you want to use. The buffer pool used determines how to cache datablocks in memory in order to facilitate faster data access. BP0 is the default buffer pool for sorting.
- Storage Group. (STOGROUP). If selected, stores the index on a data set managed by Db2 in the named storage group. Stogroup-defined index spaces let Db2 do all of the VSAM allocation work for you. If you create a Stogroup for the data model, using the Stogroup Editor (see Creating and Editing StoGroups), you can select that Stogroup from the list. If you select Stogroup, you can also define values for PRIQTY and SECQTY. To prevent wasted space for non-partitioned indexes, do not define PRIQTY and SECQTY. Instead let Db2 manage the amount of primary and secondary space allocated for the index.
- Encoding Scheme. (CCSID). Specifies the encoding scheme: ASCII, EBCDIC, UNICODE or the system-installed default. This is not valid if you intend to use this database as a work file in a data-sharing environment. If you do not want to use the default encoding scheme that was selected when you installed Db2 for OS/390 and z/OS, you can override your installation selection here. You cannot specify an encoding scheme for a workfile database.
Definition page/tab
Enter or edit a definition for the database. If the target database supports it, ER/Studio Data Architect adds this definition as a comment when generating SQL code.
DDL page/tab
Displays the CREATE DATABASE statement needed to build the database. ER/Studio Data Architect uses the platform-specific parser of the model's selected database platform to generate the database.
Attachment Bindings tab
Bind an external piece of information or attachment to the database. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list, depending on the attachment datatype. Attachments reside in the Attachments folder of the Data Dictionary. For more information, see Attaching External Documents to the Data Model.