Use
db_create to create a new
LLDB for a particular environment. If the operator approves it,
db_create also will drop an existing LLDB and replace it with new fully provisioned LLDB.
db_create may only be run by
$NSTDBA, LeasePak database administrator. It requires that the operator know the password for the
database server administrator, $SRVADM; that the operator be prepared to assign a
password to any
DBO that may be created (see note below); it requires that the
database server for the
db_type selected when the environment was created using
setup_new_env be up and running and capable of executing interactive
SQL commands; it does not require the services of the Queue Manager.
db_create may require the operator to enter a password for the DBO, the owner of the
LLDB about to be constructed. If the LLDB is being created under Oracle, or if under Sybase and the LeasePak
$SYB_AUTODBO SETUP option,
Automatically create Sybase database owner names, is set to
Y (
echo $SYB_AUTODBO at the command prompt to see), a separate database system user, with the same name as the LLDB, will be created as the DBO, and the operator will be required to provide a password for this new user. This user should not have a UNIX/Linux user account. If
$SYB_AUTODBO is
'N', the operator will be required to provide an existing Sybase user's name, and a password for this role.
As with most LeasePak db_* commands that affect a particular LLDB, db_create determines the appropriate LLDB to use via the environment parameter. which references the settings created by setup_new_env.
Common Usage
The following is the most common example of using db_create:
db_create env-name
where
-
env-name is the environment in which the LLDB will be (re)created
-
Other inputs
-
The operator will be prompted for the $SRVADM's (the database server administrator's) password. For security, the characters of the password are not printed on the screen as the operator types them.
-
The operator may be prompted for the 's password. If creating a new LLDB, the operator may be prompted to reenter the DBO's password to confirm. For security, the characters of the passwords are not printed on the screen as the operator types them.
-
The ensuing discussion will refer to
LeasePak Storage Segments; the reader is invited to study the article on
storage segments, which is located in this
System Administration Guide.
-
The operator will be prompted for the storage segments on which to construct the LLDB. The DBMS determined by the db-type that was indicated when the environment was constructed using setup_new_env is the DBMS under which the LLDB will be constructed; this in turn will determine how
db_create prompts for storage segments.
Oracle
-
Under Oracle , the operator will be presented with a list of one or more s. Each storage segment is an Oracle tablespace. An Oracle LLDB can use only one storage segment, but an Oracle storage segment can contain multiple LLDBs. Only storage segments constructed for LeasePak may be used with db_create.
-
For small LLDBs under 300MB, when prompted, the operator enters a common storage segment and specifies the required size of the LLDB in megabytes when prompted.
-
For larger LLDBs over 300MB, the System Administrator must create a dedicated storage segment specifically for the particular LLDB under construction; this along with any common storage segments will be displayed. The operator enters the dedicated storage segment when prompted and when prompted for the size, enters
UNLIMITED, which allows the LLDB to utilize the entire segment.
-
Each common storage segment can contain multiple LLDBs; each dedicated storage segment can contain only one LLDB.
-
If the Strict naming convention is in force, only common storage segments and a possible dedicated storage segment will be displayed. If the Loose naming convention is in force, then other system and/or non-LeasePak storage segments may be displayed as well.
-
The operator must be careful to
not locate segments for any
LLDB on any tablespace that was not specifically created for LeasePak.
Sybase
-
Under Sybase (
db-type of syb), the operator will be presented with a list of two or more storage segments. Each storage segment is a Sybase database device. The System Administrator must create these storage segments before LLDB creation. Two kinds of storage segments must be created: data devices and log devices. Each Sybase LLDB requires space on both kinds of storage segments.
-
When prompted, the operator enters the name of a storage segment to use, then enters the number of megabytes of space available on that storage segment to allocate to the LLDB, and then enters
D for data or L for log to indicate how the storage segment is to be used. The operator continues selecting storage segments until sufficient space has been allocated for the LLDB's needs for both data storage segments and log storage segments have been met.
-
Data storage segments must be used for only the LLDB's data segments, and log storage segments must be used for only the log segments of LLDBs. Mixing data and log from any LLDBs on the same storage segment is not allowed.
-
Each Sybase storage segment can hold parts of multiple LLDBs.
-
When setting up Sybase devices, it is strongly advised that the System Administrator choose a naming convention that will make the purpose of each device clear.
-
The operator must be careful to
not locate sgements for any
LLDB on any device that was not specifically created for LeasePak.
-
If the LLDB already exists,
db_create will ask the operator whether or not to drop the LLDB. If the operator enters 'Y' or 'y', the existing LLDB will be destroyed, and all data in it will be permanently lost unless the System Administrator has made a suitable . If the operator enters anything else, the LLDB will not be affected, and db_create will abort.
db_create Worksheet
Note the following values for running db_create
| Name |
Description |
Your Value |
Notes |
| env-name |
environment name |
|
Must have been previously created using setup_new_env
|
| $NSTDBA's password |
LeasePak Database Administrator |
|
This user is the one who has permission to run db_create. The $SRVADM's password will also be required, as well as potentially the password for the DBO..
|
| $SRVADM's password |
database server administrator |
|
This user is the one who actually runs the commands under the database server to provision and create the LLDB.
|
| $SYB_AUTODBO |
obtain from environment:
echo $SYB_AUTODBO
|
|
Determines the way the DBO is set up in the next row
|
| DBO-name |
The user to be assigned the DBO role
|
|
Required if under Sybase and $SYB_AUTODBO is N; is created
automatically otherwise
|
| DBO's password |
The LLDB owner
|
|
The DBO is the user-role who owns the objects within the LLDB and who grants regular users access to the LLDB
|
| Data size |
# of megabytes of data space needed |
|
Under both Oracle and Sybase; if under Oracle and a dedicated storage segment is to be used, should be "UNLIMITED" Under Syabse, should be the total of required data space.
|
| Storage segment |
if known |
|
Oracle: either a common storage segment or a dedicated storage segment (must be dedicated if size > 300MB);
Sybase: may not be known ahead of time
|
| Log size |
# megabytes of log space needed |
|
Sybase only |
CRITICAL NOTE
Sybase Only
db_create creates an LLDB with the truncate log on checkpoint option enabled. This will empty the database server-generated transaction logs each time an automatic checkpoint is performed by the database server. In production environments, the administrator MUST disable the Sybase truncate log on checkpoint option so that the entire transaction log is available for recovery in the event of a system failure between full backups. The transaction log is vital for recovery from system errors and crashes. It can be managed so that its disk space requirements are kept as low as possible. Refer to the Sybase Adaptive Server documentation for more information or contact the NetSol Help Desk.
Running db_create
Log on the application host as $NSTDBA, the LeasePak database administrator.
Either run
db_create with the common usage shown below, or click
here to see the complete syntax of
db_create.
[nsdba77a:~] db_create prod
The command will produce a display similar to the screen print below. For brevity we have deleted several lines (shown by "...") from this screen print; the full output of db_create for Sybase can be seen by clicking here, for Oracle by clicking here.
[nsdba77a:~] db_create prod
2011-08-13 17:52:56 db_create: Create (prod)lpr_prod
2011-08-13 17:52:56 db_create: Running commands as srvadm
Server Administrator 'srvadm' password: srvadm's password
Logical database owner 'OWNER' password: database owner's password
2011-08-13 17:53:14 db_get_dbo: Start
2011-08-13 17:53:14 db_get_dbo: Running commands as srvadm
2011-08-13 17:53:17 db_setup_phys: Set up physical storage description for (prod)lpr_prod
Storage segments with available space:
lpk_log01 160 lpk_data05 388
lpk_log02 230 lpk_data06 332
lpk_log03 173 lpk_data07 380
lpk_log04 320 lpk_data08 178
lpk_log05 581 lpk_data09 368
lpk_data04 38 lpk_data10 298
[<RET>/Q/q]=quit [R/r]=redisplay list [V/v]=view physdb.msirc
Enter Segment name from list above: lpk_data05
Enter # MBs required from segment: 150
Segment type: 'D[ATA]' or 'L[OG]': d
[<RET>/Q/q]=quit [R/r]=redisplay list [V/v]=view physdb.msirc
Enter Segment name from list above: lpk_log01
Enter # MBs required from segment: 40
Segment type: 'D[ATA]' or 'L[OG]': l
[<RET>/Q/q]=quit [R/r]=redisplay list [V/v]=view physdb.msirc
Enter Segment name from list above: q
Current contents of physdb.msirc:
setenv MSIDB_SEG01 "lpk_data05,150,DATA"
setenv MSIDB_SEG02 "lpk_log01,40,LOG"
2011-08-13 17:54:45 db_setup_phys: New physical configuration of (prod)lpr_prod stored in
$ENVDIR/etc/physdb.msirc
2011-08-13 17:57:43 db_get_dbo: Start
2011-08-13 17:57:43 db_get_dbo: Running commands as srvadm
2011-08-13 17:57:46 db_build: Build (prod)lpr_prod
2011-08-13 17:57:46 db_build: Running commands as srvadm
2011-08-13 17:57:47 db_build: Constructing logical LeasePak database - SQL portion...
2011-08-13 17:57:53 db_set_dbo: Start
2011-08-13 17:57:56 db_set_dbo: Changing DBO of (prod)lpr_prod to lpr_prod ...
2011-08-13 17:57:56 db_set_dbo: End
2011-08-13 17:57:56 db_build: End
2011-08-13 17:57:56 db_load_obj: Load logical database objects in (prod)lpr_prod
2011-08-13 17:57:56 db_load_obj: Running commands as DBO, lpr_prod
Xmap: tbl(mcml) tbl(mcmu) tbl(mja) tbl(mjc) tbl(mjl) tbl(mlt) tbl(msg)
Xmap: tbl(msvb) tbl(msvc) tbl(msvh) tbl(msvi) tbl(msvl) tbl(msvr) tbl(msvs)
Xmap: tbl(msvu) tbl(msvv)...
...
Xmap: idx(mja) idx(mjc) idx(mjl) idx(mlt) idx(msg) idx(msvb) idx(msvc)
Xmap: idx(msvh) idx(msvi) idx(msvl) idx(msvr) idx(msvs) idx(msvu) idx(msvv)
Xmap: idx(mwa) idx(mwc)...
...
Xmap: idx(rzp) idx(rzq) idx(rzu)
2011-08-13 18:01:24 db_load_obj: Loaded nnn object scripts into (prod)lpr_prod
2011-08-13 18:01:24 db_load_obj: End
2011-08-13 18:01:24 db_load_code: Load SQL code in lpr_prod for prod
2011-08-13 18:01:24 db_load_code: Running commands as DBO, lpr_prod
2011-08-13 18:01:24 db_load_code: Creating ordered list of files to load ...
2011-08-13 18:01:25 db_load_code: Creating alphabetical list of up* code objects ...
2011-08-13 18:01:25 db_load_code: Creating alphabetical list of authorized cp* code objects ...
2011-08-13 18:01:25 db_load_code: Replacing authorized cp* objects with user-provided versions .
2011-08-13 18:01:25 db_load_code: Creating alphabetical list of main procedures to be loaded ...
2011-08-13 18:01:25 db_load_code: Creating final list(s) of procedures to be loaded ...
2011-08-13 18:01:25 db_load_code: Rebuilding views ...
2011-08-13 18:01:32 db_load_code: Loading SQL Code Objects ...
Xproc: aacompare(dates) lessee(add_remove) alt(cussum) ar(dtl) chk(upd) mjc(convert) mp(davox)
Xproc: rase(dbaconv_ras) dbaconv(rsc) related(del_app) upd(drawdown_clnt) ext(ela_asset)
ext(ela_codes) hist(ela)
...
Xproc: upd(allowed) update(passwd) xml(lsesum) mt(mcml) mt(mcmu) mt(mja) mt(mjc)
Xproc: mt(mjl) mt(rad) mt(raf) mt(rag) mt(rai) mt(ral) mt(rap)
Xproc: mt(rvd) mt(rxr) mt(rxu) mt(rzg) alt(lsesum) cp(altlsepayinfo) asset(dtl)
Xproc: asset(sum) col(lsesum) cus(leases) cus(lsesum) mat(lsesum) rap(der) rec(lsesum)
Xproc: rfw(notes) rgc(info) susp(info)
2011-08-13 18:13:00 db_load_code: Loaded nnn source files into (prod)lpr_prod
2011-08-13 18:13:01 db_load_code: End
2011-08-13 18:13:01 db_set_security: Set db security in (prod)lpr_prod
2011-08-13 18:13:01 db_set_security: Running commands as lpr_prod
2011-08-13 18:13:01 db_set_security: Setting general db security in (prod)lpr_prod
2011-08-13 18:13:16 db_set_security: End
2011-08-13 18:13:16 db_create: End
Below is the initial part of a run of db_create under Oracle. It shows how db_create will prompt for the DBO's password a second time if the DBO is new, plus it shows how the selection of the storage segment appears under Oracle. In this case a dedicated storage segment has been provided. After the selection of the storage segment, the process continues in a manner similar to that of Sybase. The full output of db_create under Oracle can be seen by clicking here.
[nsdba77a:~] db_create prod
2011-08-13 23:06:09 db_create: Create (prod)lpr_prod
2011-08-13 23:06:09 db_create: Running commands as srvadm
Server Administrator 'srvadm' password: srvadm's password
Logical database owner 'OWNER' password: database owner's password
2011-08-13 23:06:23 db_get_dbo: Start
2011-08-13 23:06:23 db_get_dbo: Running commands as srvadm
Retype password to confirm; leave blank to restart password selection ...
Confirming Database Owner 'OWNER' password: database owner's password
2011-08-13 23:06:34 db_setup_phys: Set up physical storage description for (prod)lpr_prod
Storage segments with available space:
LPCOMMON LPT_PROD
[<RET>/Q/q]=quit [R/r]=redisplay list [V/v]=view physdb.msirc
Enter Segment name from list above: LPT_PROD
Enter # MBs required from segment: UNLIMITED
Current contents of physdb.msirc:
setenv MSIDB_SEG01 "LPT_PROD,UNLIMITED"
2011-08-13 23:07:01 db_setup_phys: New physical configuration of (prod)lpr_prod stored in
$ENVDIR/etc/physdb.msirc
