db2it - IBM i

db2it Software

Trigger History

Who/What/When/Where/Why/How
+ Synchronize Changes

Journal History

Who/What/When/Where/Why/How
+ Synchronize Changes

Audit History

Who/What/When/Where/Why/How
+ Monitor Security

Event History

Who
What
When
Where
Why
How

Transaction History

Who/What/When/Where/Why/How
+ Synchronize Changes

db2it ERP

Finance

People

Manufacturing

Supply Chain

Fundamentals

Database
Classes/Properties/Actions/Transactions
Business Rules

Transactions
Start Transactions
Enforce Business Rules PerformActions/PendingActions
Rollback/Commit

Menus
Create Menus/Options/Fastpaths to perform
Actions/Transactions

Security
Grant Groups authority to perform Actions/Transactions

Platforms

Web Sites

Web Services

Mobile

PC

Alerts

Email

Refreshable Spreadsheets

PDF Reports

db2it Tools

Work with Sql Results

Work with Objects

Work with Object References

Work with Cross Reference

With with Fields

Work with Files

Work with Database Relations

Work with Data Members

Work with Source Members

Work with Commands

Work with Menus

Work with Options

Work with Fastpaths

Work with Audit History

Work with Journal History

Work with Trigger History

Purge

IFS Files
Journal Receivers
Physcial File Members
Deleted Records (Reorganize)

Transaction History
Trigger History
Journal History
Audit History
Events

Performance


Work


SnapShot

Capture System Settings
Validate Role Swaps
Validate System Upgrades

Record Locks

Message + Email

"Please Exit"
"Please Exit"
"Please Exit - Last Chance"

ENDJOB

Events

Log Events to track requests, results, statistics...

EvtID start timestamp
Event name
Library object library
Object object name
Type object type
Seconds duration
State success/failure...
Data event data
MsgID message identifier
MsgDta message data
Job number/user/name

Archive

Archive/Purge to boost performance?
Compress to reduce storage?

select *
from table
 asof timestamp
 asif fork

Save & Restore Triggers


Refresh Environments

Test
Training

Systems

  • Menu System

    • Menus

      • AddMnu

      • ChgMnu

      • RmvMnu

      • RunMnu

    • Menu Options

      • AddMnuOpt

      • ChgMnuOpt

      • RmvMnuOpt

      • RunMnuOpt

    • Fast Paths

      • RunFstPth

  • Security System

    • Sql Command (CMD)

    • UsrAut *SrvPgm

    • Groups

    • Users

  • Transaction System (AsOf/AsIf)

    • Error Handling

    • Enforce Rules at database level

      • UK/PK/FK

      • Check constraints

      • Dynamic constraints

        • Row

        • Statement

        • Transaction

    • Rollback/Commit Transactions

      • Add Commit Actions (AddCmtAct)

    • AddTrnCst

    • ChgTrnCst

    • RmvTrnCst

    • HldTrnCst

    • RlsTrnCst

    • StrTrn

      • Insert

      • Update

      • Delete

      • AddCmtCmd

    • RbkTrn/CmtTrn

Software

  • Work Management

    • Refresh

    • CvtJob

    • RtvSbmJob

    • StrSys

    • EndSys


  • Save & Restore

    • SnapShot

  • Replace Bad Data (CCSID 37 00-3F with 40)(RplBadDta)

  • Allocate Reorganize Physical File Member Deallocate (AlcRgzDlc)

  • Triggers

    • Add Replace Trigger (AddRplTrg)

    • Save Triggers (SavTrg)

    • Restore Triggers (RstTrg)

  • Check Active Jobs (ChkActJobs)

  • Check Level Checks (ChkLvlChks)

  • Check Sign On

  • Fix Level Checks (FixLvlChks)

  • Email (SndPhpMail)

  • End Job (EndJob)

  • Purge IFS (PrgIFS)

  • Purge Members (PrgMbr)

  • Refresh Environments (Refresh)

  • Resolve Record Locks (RcdLck_Resolved)

  • Refreshable Spreadsheets (ODBC+SQL)

  • Audit History

    • DspAudHst

    • HldJrnHst

    • RlsJrnHst

    • PrcAudHst

    • PrgAudHst

  • Job History

    • DspJobHst

    • HldJobHst

    • RlsJobHst

    • PrcJobHst

    • PrgJobHst

  • Journal History

    • DspJrnHst

    • AddJrnHst

    • ChgJrnHst

    • RmvJrnHst

    • HldJrnHst

    • RlsJrnHst

    • PrcJrnHst

    • PrgJrnHst

  • Trigger History

    • DspTrgHst

    • AddTrgHst

    • ChgTrgHst

    • RmvTrgHst

    • HldTrgHst

    • RlsTrgHst

    • PrcTrgHst

    • PrgTrgHst

  • Transaction History

    • DspTrnHst

  • Events

    • LogEvent

    • PostEvent

    • PrgEvents

    • GetEvent

    • WaitEvent

  • Journal Management

    • AddJrnMgt

    • RmvJrnMgt

    • PrcJrnMgt

  • SQL

    • CL Commands

      • RtvSqlCnt

      • RtvSqlVal

      • RtvDate

      • RtvTime

      • RtvSbmJob

    • Functions


    • Table Functions

      • List

      • Objects

      • Members

      • Source

      • TblChanges?

      • TblTotals?

      • TrgHstChgs_Start

      • TrgHstChgs_Complete

    • Tables

      • $Dates

      • $Times

      • $OneRow

    • Procedures


  • Work with Query (WrkQry)(after invoking ChgQryA to impose limits for interactive job)

  • Work with *File/*QryDfn/*QmQry using SQL (Wrk)

    • Work with Field (WrkFld)

    • Work with DataBase Relations (WrkDBR)

    • Work with Object References (WrkObjRef)

    • Work with Object Cross References (WrkObjXRef)

    • Work with Source Members (WrkSrcMbr)(Src)

    • Work with Source or Data Members (WrkMbr)

Commands


Service Programs

  • EnvBox

  • PgmMsg

  • StrBox

  • MsgBox

  • WdwBox

  • TrgHst

  • RcdLck

  • MnuSys

  • TrnSys

  • SecSys

SQL Functions, Scalar

  • List( selectstatement, delimiter )

  • ObjectText - GetObjTxt( Lib, Obj, Typ )

  • ObjectExists( Lib, Obj, Typ )

  • ObjectLibrary - GetObjLib( Obj, Typ )

SQL Functions, Table

  • Libraries

  • Objects

  • Files

  • Members

  • Source + Indented

  • Users

  • Commands

  • Programs

  • Program_BoundPrograms

  • ServicePrograms

  • ServiceProgram_BoundPrograms

  • JobDescriptions

  • JobQueues

  • MessageFiles

  • MessageFile_Descriptions

  • MessageQueues

  • MessageQueue_Messages

  • RecordLocks

  • Rows( rows ) -- return rows


SQL Procedures

  • TrgHstChgs_SyncStart( lib, file, keys, since, thru )

  • TrgHstChgs_SyncRestart( lib, file, keys, since, thru )

  • TrgHstChgs_SyncComplete( lib, file, since, thru )

  • TrgHstChgs_SyncCancel( lib, file, keys, since, thru )

  • TblChanges

  • TblTotals

SQL Tables

  • $Dates

  • $Times

  • $OneRow

Suggestions

  • Need System to do something?

  • Need Change Management software?

  • Need to download data into spreadsheets?

db2it Design

  • Data

    • Current Settings vs History

    • Do NOT Delete? Delete but use Transaction History

  • Database

    • All Classes/Tables have PK/FK (referential integrity)

      • Indexes

      • Views

      • History (AsOf)(AsIf?)

    • All Class Properties (ClsPrp) are defined in Properties (Prp)(data dictionaries)

    • All Actions are Transactions (Rollback/Commit)

  • Structured Programming

    • All Data Structures/Fields/Columns/Properties are Qualified

    • All Code requires Error Handling

      • Do NOT Ignore Errors

      • Do NOT prompt for input after starting transaction

    • Avoid Goto

    • Avoid Literals (use named constants)

    • Avoid Indicators

    • Avoid Smart Numbers

  • All Interfaces

    • Use Dynamic Controls

db2it Asof/Asif Database

ID

Cast(Unique Timestamp as BigInt) as ID PK
Cast( ID as Date) as ID_Date
Cast( ID as Time) as ID_Time
Cast( ID as Timestamp) as ID_Timestamp
EmpID YYYY-MM-DD HH:MM:SS ZZZZZZ

Does this table need to exist? No
but it provides fast way to validate ID.
Rel table provides its dependent relations.
ID is ClsObj(Emp)
ID is ClsObj(Brc)
ID is Ord.ID

!@#$ How do we avoid logging bazillion updates?

Trn
ID PK (FK ID.ID)

Trg
ID PK (FK ID.ID)
TrnID (FK Trn.ID)
ActID (FK Act.ID)

Rel

Relation

RelID:


ID
relation
ID

Database Rules
Sys are UK
Lib are UK
Cls are UK
Obj are UK
Prp are UK
Val are UK
Sys.Lib are UK
Lib.Cls are UK
Cls.Prp are UK
Prp.Val are UK
Cls.Obj are UK
Cls.Obj.Prp are UK
Cls.Obj.Prp.Val are UK

!@#$ --- no --- !@#$
ID
parent of child
ID
!@#$ --- no --- !@#$

ID child
of
ID parent

ClsID
has
ObjID

ClsID
has
PrpID

ClsID
supports
ActID

!@#$ could one table do everything?
ASOF/ASIF/ID/REL/VAL (each record is UK)
Other databases could easily:
sync changes?
save changes?
restore changes?
undo changes?

select uncommitted|committed rows
from rel
asif asif
asof asof
where rel.ID = 'ClsObj'
and rel.rel = 'has'
and rel.val = Val('

select by a,b,c
group by a,b,c
order by a,b,c

optimize for 10 rows
fetch first 10 rows only


---------------------------------------------

ID is Cls(*)=EmpMst
ID is Prp(*)=EmpNo
ID is Cls(EmpMst).Prp(EmpNo)
ID is Cls(EmpMst).Prp(EmpNo).Val(*)=123

Cls(ID).Obj(ID).Prp(ID).Val(ID) is 123
Cls(ID).Obj(ID).Prp(ID).Val(ID) is 123
Cls(ID).Obj(ID).Prp(ID).Val(ID) is 123
Cls(ID).Obj(ID).Prp(ID).Val(ID) is 123
Cls(ID).Obj(ID).Prp(ID).Val(ID) is 123
Cls(ID).Obj(ID).Prp(ID).Val(ID) is 123
Cls(ID).Obj(ID).Prp(ID).Val(ID) is 123
Cls(ID).Obj(ID).Prp(ID).Val(ID) is 123

ID is
ID is
ID is
ID is

DtaTyp

Data Types

ID
Cast( Unique Timestamp as BigInt )
(unique creation timestamp)
(maximize performance)
(minimize storage)
(consistent link)
(supports 1,000,000 unique values per second)
BigInt 8 bytes (fastest)
dec(26) 13 bytes (fastest)
char(26) 26 bytes (slowest)
YYYYMMDDHHMMSS.ZZZZZZ
86400 s/d
31557600 s/y
315544442400 s/9999y
12+6=18 digits vs 19 digits

Date
yyyy-mm-dd
0001-01-01
9999-12-31

Time
hh:mm:ss
00:00:00
24:00:00

Timestamp
yyyy-mm-dd hh:mm:ss.zzzzzzzzzzzz
0000-01-01 00:00:00:000000000000
9999-12-31 24:00:00:000000000000

Integer(n)
n=1TinyInt, 2SmallInt, 4Int, 8BigInt bytes
-9,223,372,036,854,775,808
+9,223,372,036,854,775,807

Character(1-32)
VarChar(n)
CLOB(n)

Decimal(d,p)
d=1-63 digits
1-32 bytes

Numeric(d,p)
Graphic(n)
VarGraphic(n)
DBCLOB(n)
Binary(n)
VarBinary(n)
BLOB(n)

Small Tables?
Properties greater than 32 bytes require:
VarChar(33-32,739)
VarClob(33-2,147,483,647)


AsOf

sys.*.lib.*.cls.*.obj.*

Commands (we like named parameters)
Add.lib/EmpMst a(1) b(2) c(3)
Add.EmpMst a(1) b(2) c(3)
Chg.EmpMst(1) b(3)
Dlt.EmpMst ID(1)

Dsp.EmpMst where('Emp=123')
Prt.EmpMst where('Emp=123')
Wrk.EmpMst where('Emp=123')
Edt.EmpMst(1)

Acquire edit locks?
Edt(*lock).EmpMst(1)
Edt(*nolock).EmpMst(1)

Start CreateSalesOrder (Transaction)
 Add.SOHdr a(1) b(2) c(3)
Cmt.Action1
 Add.SODtl l(1) i(1) a(1)
Trg.Chg.SOHdr lines(+1)
Cmt.Action2
Add.SOAdr address('123 Test St')
Commit/Rollback (Transaction)
Release transaction locks?
Release edit locks?

AsIf

DB States
Past
Present
Future

Real (asif id)
Imaginary (asif null)

How do we support gathering input for future transactions?
temporary tables
temporary data structures

How do we support states?
open/close
active/inactive
enabled/disabled


Business Rules






Cls

Class
ClsID

Prp

Property
PrpID

Val

Value
ValID

Obj

Object
ObjID

Act

Action
ActID

Aut

Authority
AutID

ClsPrp

Class Property
ClsID
PrpID

ClsAct

Class Action
ClsID
ActID

ClsObj

Class Object
ClsID
ObjID

ClsPrpVal

Class Property Value
ClsID
PrpID
ValID

ClsObjAct

Class Object Action
ClsID
ObjID
ActID

ClsObjPrpVal

Class Object Property Value
ClsID
ObjID
PrpID
ValID