×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Database Sizing

How do I size the object security tables? by blakej
Posted: 25 Jun 01

(Copy from the MIMS Open Enterprise Forum)

Sizing / Re-sizing Security Tables for MIMS/Ellipse.

The MIMS/ELLIPSE application security is configured in numerous database tables, the  most importing being the following six:
   +MSF02A - Security Profile Application, the records in this table contain all client applications that a profile (as defined on the security profile MSF020) has been granted access to.
   +MSF02B - Security Reference Code Entity, the records in this table contain all the reference code entities ( +TYPES , of client applications)  that a profile (as defined on the security profile MSF020) has been granted access to
   +MSF02C - Security Profile Class
   +MSF02D - Security Profile Class Attribute
   +MSF02E - Security Profile Class Method, the records in this table  contain all class methods that a profile (as defined on the security profile  MSF020) has been granted access to. The method of security implemented by the Project Team will determine the  final size of these tables.  Key factors are:
       + the number of users and/or profiles;
       + the type of profiles (i.e. Global or User);
       + the number of districts;
       + the number of entities (i.e. programs or screens); and
       + the degree to which access to screen attributes is limited or +customized

  MSF02A
The appropriate size for the MSF02A table may be determined by multiplying the  total number of applications (MSQ ,s) by the number of entities (Profiles,  Global Profiles, and/or Users).  For this particular database it was  determined that there are 80 MSQ ,s and there will be approximately 30
users/Global Profiles.  Thus, the expected row count for this table is:
80 X 30 = 2400 rows
A table description and some notes are below.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> desc msf02a
 Name                                      Null?    Type
 ----------------------------------------- --------
 ENTRY_TYPE                                NOT NULL VARCHAR2(1)
 ENTITY                                    NOT NULL VARCHAR2(10)
 DSTRCT_CODE                               NOT NULL VARCHAR2(4)
 APPLICATION_NAME                          NOT NULL VARCHAR2(20)
 APPLICATION_TYPE                          NOT NULL VARCHAR2(1)
 ACCESS_LEVEL                              NOT NULL VARCHAR2(1)

SQL> select count(*) from msf02a;

  COUNT(*)
----------
      1312

SQL> spool off

SQL> select distinct(entity) from msf02a;

ENTITY
----------
USER1
USER2
USER3
NINES
ADMIN
USER4
USER5
RANDY

8 rows selected.

79(MSQ's) * 20(Global Profiles) = 1580

Bump it up for 'cushion':

80 * 30 = 2400

  MSF02B
The appropriate size for the MSF02B table may be determined by multiplying the total number of REFCODE_ENTITY*** (SQL STATEMENT:  SELECT DISTINCT RCODE_NAME FROM MSF02M;) by the number of districts by the number of Profiles, Global Profiles, and/or Users.  For this particular database it was determined that there are 16 REFCODE_ENTITY, 5 districts, and there will be approximately 30 users/Global Profiles.  Thus, the expected row count for this table is:
16 X 5 X 30 = 2400 rows
A table description and some notes are below.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> desc msf02b
 Name                    Null?    Type
 ----------------------- --------
 ENTRY_TYPE              NOT NULL VARCHAR2(1)
 ENTITY                  NOT NULL VARCHAR2(10)
 DSTRCT_CODE             NOT NULL VARCHAR2(4)
 REFCODE_ENTITY          NOT NULL VARCHAR2(3)***FK on MSFO2M
 ACCESS_LEVEL            NOT NULL VARCHAR2(1)

SQL> select count(*) from msf02b;

  COUNT(*)
----------
       256

SQL> spool off

16 (REFCODE_ENTITY) * 5 (DSTRCT_CODE) * 30 (Global Profiles) = 2400

   MSF02C        
The appropriate size for the MSF02C table may be determined by multiplying the total number of Profiles, Global Profiles, and/or Users by the number of districts by the number of CLASS_NAMES*** (SQL STATEMENT:  SELECT DISTINCT  CLASS_NAME FROM MSF02E;).  For this particular database it was determined that there will be approximately 30 users/Global Profiles, 5 districts, and 91 CLASS_NAMES.  Thus, the expected row count for this table is:
30 X 5 X 91 = 13650 rows
A table description and some notes are below.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> desc msf02c
 Name                      Null?    Type
 ------------------------- --------
 ENTRY_TYPE                NOT NULL VARCHAR2(1)
 ENTITY                    NOT NULL VARCHAR2(10)
 DSTRCT_CODE               NOT NULL VARCHAR2(4)
 CLASS_NAME                NOT NULL VARCHAR2(16)         ***From  MSF02E
 XDATA                     NOT NULL VARCHAR2(1)

SQL> select count(*) form msf02c
   COUNT(*)
----------
      2128

SQL> spool off

30 (Global Profiles) * 5 (Districts) * 91 (Class_Names***) = 13650

   MSF02D        
The appropriate size for the MSF02D table may be determined by multiplying the total number of Profiles, Global Profiles, and/or Users by the number of  districts by the number of CLASS_NAMES*** (SQL STATEMENT:  SELECT DISTINCT CLASS_NAME FROM MSF02E;).  For this particular database it was determined that there will be approxamately 30 users/Global Profiles, 5 districts, and 91 CLASS_NAMES.  Thus, the expected row count for this table is:
30 X 5 X 91 X 2 = 546,000 rows
A table description and some notes are below.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


SQL> desc msf02d
 Name                       Null?    Type
 -------------------------- --------
 ENTRY_TYPE                 NOT NULL VARCHAR2(1)
 ENTITY                     NOT NULL VARCHAR2(10)
 DSTRCT_CODE                NOT NULL VARCHAR2(4)
 CLASS_NAME                 NOT NULL VARCHAR2(16)
 CLASS_ATTRIBUTE            NOT NULL VARCHAR2(50)
 ACCESS_LEVEL               NOT NULL VARCHAR2(1)

SQL> select count(*) from msf02d
  COUNT(*)
----------
         0

SQL> spool off
(PROFILES/ENTITIES) * (DIST) 5 * (CLASSES) 91 * 2 (ACCESS LEVEL) = 546,000

  MSF02E        
The appropriate size for the MSF02E table may be determined by multiplying the  total number of needed for one completely configured ADMIN account by the  total number of users/Global Profiles.  Thus, the expected row count for this table is:
3325 X 30 = 99,750 rows
A table description and some notes are below.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> desc msf02e
 Name                 Null?    Type
 -----------------------------
 ENTRY_TYPE           NOT NULL VARCHAR2(1)
 ENTITY               NOT NULL VARCHAR2(10)
 DSTRCT_CODE          NOT NULL VARCHAR2(4)
 CLASS_NAME           NOT NULL VARCHAR2(16)
 CLASS_METHOD         NOT NULL VARCHAR2(50)
 SECURITY_ACCESS      NOT NULL VARCHAR2(1)

SQL> select count(*) from msf02e
  COUNT(*)
----------
     10640

SQL> spool off

3325 = row  count for one configured admin profile

3325 * 30 (num global profiles) = 99,750


The PL/SQL scripts needed to create these tables and indexes with the proper storage parameters can be generated with the msgendd.pl PERL script.  This script will accept table names as arguments on the command line.  The example
below will generate the scripts for tables MSF02A, -B, and -C.
(/bin/ksh) mimstest> msgendd.pl -defdd MSF02A MSF02B MSF02C
In cases where the database has already been created, the changes in storage parameters may also require a change in tablespace(s) (TABS & INDX) size.  To determine how much additional tablespace is needed, you may first generate the PL/SQL scripts without the new row count estimates.
The output below shows the dbase.sql script for MSF02A, -B, -C, -D, & -E with
 default storage parameters.
(/bin/ksh) mimstest> more dbase.sql
      CREATE TABLESPACE TABS DATAFILE 'tabs.dbf' SIZE 55910;
      COMMIT;
      CREATE TABLESPACE INDX
             DATAFILE 'indx.dbf' SIZE 58573;
      COMMIT;
Thus, the datafile size necessary for the tablespace for these tables ONLY would be 55,910 bytes (54.6 kilobytes).
The output below shows the dbase.sql script for MSF02A, -B, -C, -D, & -E with
 appropriate storage parameters based on the new estimated row counts.
(/bin/ksh) mimstest> more dbase.sql
      CREATE TABLESPACE TABS
             DATAFILE 'tabs.dbf' SIZE 107899080;
      COMMIT;
      CREATE TABLESPACE INDX
             DATAFILE 'indx.dbf' SIZE 111937944;
      COMMIT;
Thus, the datafile size necessary for the tablespace for these tables ONLY would be 107899080 bytes (105,370 kilobytes or 102.9 megabytes).  Since the
 database was created with the default allocation of 54.6 KB for these tables, we need an additional 105,315 KB (102.8 MB) for these tables.
107899080  ) 55910 = 107843170
107843170/1024 = 105315.6 KB
105315.6/1024 = 102.8 MB
Similar changes will be necessary in the index tablespace(s).


Back to Mincom: Ellipse FAQ Index
Back to Mincom: Ellipse Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close