IBM Support

Database Engineering topics



List of GitHub Gists by Scott Forstie and iSee video tutorials by Scott Forstie and Tim Rowe to aid Data Base Engineers (DBEs)


You are in: IBM i Tutorials, Demos, and SQL examples > Database Engineering topics
GitHub Gists Topic
Read only views.sql
Some views can be used to INSERT, UPDATE or DELETE data in an underlying physical file or SQL table. This example shows how the Database Engineer can construct the SQL view to be read only.
Calculating an ALLOCATE value for a column.sql
Database Engineers sometimes need to identify data models with varying length columns, where the allocate clause could be improved. When you use the ALLOCATE(n) clause, you're telling the database to establish n number of bytes for the column in the fixed portion of the record. If the column value for a row has a length > n, the database uses the…
Varying length columns
Temporalize a library.sql
System period temporal tables were added as a feature built into Db2 for i with IBM i 7.3. This example shows how Temporal could be established for all database files within a specific library.
Temporal tables
Mask birthdays with RCAC
This example shows how to use a Column Mask to prevent users from seeing the actual birthdate.
Row and column access control
Glengarry Glen Ross - SQL Style
Ok movie buffs, here is a fictional before and after, with a happy ending. Use these techniques to bring database engineering to bear in your data center.
Change trigger programs in production.sql
This example shows how to use the ALLOW_DDL_CHANGES_WHILE_OPEN QAQQINI option within a specific job to avoid the requirement of needing an exclusive lock for the *FILE object, before making a change to a trigger program.
MTI related Index advice
Maintained Temporary Index related index advice since the last IPL
Maintained temporary indexes (MTIs)
Largest MTIs in use today.sql
Maintained Temporary Indexes (MTIs)... everyone has them, but they're similar to building a house on sand... your foundation for performance is not rock solid. Use this Gist to gain some insight into this topic! 
Maintained temporary indexes (MTIs)
Tracking ALLOBJ users through time
The idea of this Gist is to take a step beyond access to a live view of who has *ALLOBJ user special authority, to also being able to see how the topic is changing over time. With the addition of a time dimension, clients can more easily focus on the delta changes.
Temporal Tables
Auditing columns
Hidden columns
Kitchen Sink for the Admin.sql
More SQL than you want.... or so much fine SQL that you keep coming back? Time will tell.
Physical File Members
Index Advice
Row level auditing.sql
I was asked how to incorporate row level auditing detail into tables. While Temporal tables with Generated columns is a powerful combination, the following example demonstrates a different approach.
 Hidden columns
 Default values
SQL DDL with nc.sql
SQL DML includes the WITH NC clause to avoid having the data change participate in the transaction. SQL DDL does not include the WITH NC clause, but the savvy SQL user can leverage an AUTONOMOUS procedure to achieve the same behavior.
Transaction Avoidance
Virtually done.sql
Does your physical data model include a virtual layer? If no, this gist is for you...
Modernizing with Views
The idea... discover MTIs and replace them with permanent indexes
Maintained temporary indexes (MTIs)
iSee Video Tutorials Topic
Convert your DDS to DDL
Many of us have DDS today that still is used to describe our data. We have asked, 'how to I convert that to DDL?' , 'Is there some magic tool?' .   In this session we are not going to show you any magic, but we are going to look at some methods that will empower you to be able to take those key files created with DDS and easily convert them to a table with DDL. This is an important step in being able to take advantage for the latest in database technology. 
Extract your DDL and Save it
We all have great procedures for saving the data on our systems. But if something were to happen to your database table, you might have the data, but do you have the DDL to be able to easily rebuild everything ?   In this session we will look at how to easily extract the DDL such that you have that key description information for the future.
Reverse engineering SQL objects
Understand trending of *ALLOBJ users with Temporal
In this iSee we will look at a real example of how Temporal tables can be leveraged in a simple way to 'Audit' and understand the trends of users that have *ALLOBJ Special authority.  This gives you a template that can not only be used directly, but with some simple modification can be used to understand a trend for virtually anything on your system. Knowledge is power, this iSee give you insight, which is knowledge helping you better figure out what is actually happening on your IBM i.
User Profiles
Tim and Scott branch out a little in this session. While we are still using ACS and SQL we are using them to accomplish a new purpose.  How to provide masking of data in a database table.  If you have sensitive data that people should  not have access to, this session is for you.  We will show you using RCAC how you can control at the Data level who can see data and what part of the data they should have access too. The best part, while we are using SQL to set this up and demo things, this can be applied to either a DDS or DDL based table!
Column masks
How to Leverage RCAC Row Permissions
In this session, Tim and Scott take a look at the 2nd half of Row Column Access Controls - Row Permissions. We dive into how to setup and activate this support and show you what it looks like to prohibit a user from accessing data in a database table.  The source and examples are provided which should give you a great head start in enabling this on your favorite database table.
Row permissions
Who is your Supervisor for your Queries ?
Ever had some one submit a query on your system that ran wild ?  Took over your machine and maybe even effected your other production work ? This iSee video will introduce you to a automated, real time Query Supervisor.  See how you can automatically stop run away queries and take real actions!  A great new System Management tool to add to your tool belt.
Query Supervisor

[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000C4BAAU","label":"IBM i"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]

Document Information

Modified date:
03 June 2022