News
Abstract
List of GitHub Gists by Scott Forstie and iSee video tutorials by Scott Forstie and Tim Rowe to aid Data Base Engineers (DBEs)
Content
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. |
Views |
| 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. |
Modernization |
| 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. |
Triggers |
| 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. |
Triggers 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 |
| mti_info.sql 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. |
Modernization |
| 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"}]
Was this topic helpful?
Document Information
Modified date:
03 June 2022
UID
ibm16347648