Creating and Editing DataBases

From ER/Studio Data Architect
Jump to: navigation, search

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 the following tasks are performed:

  • System catalog tables required by the database are setup.
  • The database recovery log is allocated.
  • The database configuration file is created and the default values are set.
  • The database utilities are bound 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, 6.x, 7.x, 8.x, 9.x, 10.x, and 11.x

Notepad blue icon 2.pngNote: 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

  1. In the Data Model Explorer, expand a physical model, right-click the Databases node, and then click New Database.
  2. Complete the Database Wizard and then click Finish to create the database.
  3. Once you have created 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 that will be used for purposes such as managing the data of temporary tables, storing intermediate SQL results, created 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 to be used. The buffer pool used determines how the datablocks are cached 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 the VSAM allocation work for you. If you have created 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, you should 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 this database is to be used 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 have 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 are created in the Attachments folder of the Data Dictionary. For more information, see Attaching External Documents to the Data Model.

See Also