db2it - IBM i

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

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

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

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

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

Transaction History
Trigger History
Journal History
Audit History
Events

Capture System Settings
Validate Role Swaps
Validate System Upgrades

Message + Email

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

ENDJOB

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/Purge to boost performance?
Compress to reduce storage?

select *
from table
asof timestamp
asif fork

Test
Training

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)

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

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)

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?

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

Class
ClsID

Property
PrpID

Value
ValID

Object
ObjID

Action
ActID

Authority
AutID

Class Property
ClsID
PrpID

Class Action
ClsID
ActID

Class Object
ClsID
ObjID

Class Property Value
ClsID
PrpID
ValID

Class Object Action
ClsID
ObjID
ActID

Class Object Property Value
ClsID
ObjID
PrpID
ValID