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) |
| Undocumented IBM i Services UDTFs.sql This request has come in many times: Provide a UDTF alternative to a an SQL View for some of the IBM i (SQL) Services. My response is that the UDTFs already exist, are sometimes not documented, and all times are OK for users to query directly. |
Undocumented IBM i Services UDTFs |
| Decimal column checker upper.sql The request was this... I want to see how close some internal identification columns are to maxing out their maximum value. For example a counter that’s defined as DECIMAL(7,0) has a high value of 9,995,000 would indicate that we need to intercede ASAP. |
Maximum column value |
| Reactive index strategy.sql Database and SQL performance tuning is a persistent focus, which many times leads into the indexing strategy. In this gist, I show how some of the existing tools can be tied together to achieve an automated "DBE in a box". |
Indexes |
| 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 |
| How to Use RCAC Column Masking
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 |
| Modern Application Part 1 - The Data Model Applications are the life blood of our business. The world of applications has changed a great deal of the past few years. Well, at least they should have changed. In this and the next few videos, we are going to look at a few different aspects of applications. This session will explore some of the new features, technologies, and patterns for data. Scott is going to take us though some key database ideas and concepts and some very practical code snippets to give you a head start on using these techniques in your own world. |
Modernization |
| Modern Application Part 2 - Rest APIs In this session we will take a look at creating Rest Apis to access and and update data within the databases that we created in the last session. |
Modernization |
| Modern Application Part 3 - The View In this concluding session, we present a quick and easy way to leverage modern languages and frameworks to pull it all together. We include the code to help you get started on this journey. | Modernization |
| 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 |
| Journal Receiver Growth Monitor We all have Journal Receivers, and I am sure you all at some point have wondered how large are they. Which of course is nice to know, but what if you could watch the and monitor the growth of your Journals over time.... Now, that might not only be investing, but could prove enlightening! In this session, Scott and Tim look at how to solve this problem. This iSee is really a template that you could use to watch and monitor growth of not just Journal, but pretty much anything you might find interesting. We are putting Temporal tables to work in a practical way. This video shows you how simple it is to accomplish. When you are done you have working framework, today showing Journal receiver growth, but easily leveraged for your favorite topic in the future. |
Journal receivers |
| Using Views to Unlock Your Physical Files When is comes to modernizing your applications, it can be a very daunting task. This also include modernizing your data. We know that there are still many physical files out in the wild and converting them to a new DDL based table while can be very freeing, is a huge task. The good news, is using Views, you can start to unlock that data and not have to also modernize that applications that have been using these physical files. In this iSee, we will look at an automated way to create a set of views over a number of physical files. This will not only give you this automated process, but we then also talk about leveraging these View in new ways. |
Modernization |
| iSee, Actually I Don't!! Where did the Db2 Health Center Go?? The Db2 for i Health Center helps you with understanding and keeping your database healthy and happy. If you have used this interface in the past, it was part of the Heritage Navigator interface, but won't be found in the New Navigator as database features are found within IBM i Access Client Solutions (ACS). So, where do you look within ACS to find the Db2 Health Center? This session will help you first, find the Health Center, and more importantly help you see why its a must to keep the "heart of the system" beating in a happy rhythm. |
Db2 Health Center |
| iSee Database Catalogs - A True Treasure Like many of these videos we want to show you something new, not just for the sake of new.. but because its better. For this session many make use of the information provided in the DSPFD - Display File Description CL Command. Now, for sure there is useful information in that, but in this session we are going to take a closer look at what database catalogs have to offer. I think after we are done, you will agree they are a true treasure chest of goodness. |
Database catalogs |
| iSee Rest APIs, from Dad Jokes to sending Text messages to your phone! In this session Scott and Tim have some fun with Rest APIs. The IBM i operating system has a built in Rest API engine combined with SQL you have a simple powerful way to call a Rest API out in the InterWeb. Yea… this means that you can do this with RPG and COBOL by simply imbedding the SQL in your ILE programs. We show you a fun API that returns a ‘Dad joke’. Think of how that can be used!!! Add a fun bit of humor to your application! Then we dive in looking at Geo position data. Using a street address, find your geo-location. Last we really spice it up by showing you a simple service that can be leveraged to send text messages to your phone from the IBM i!!! Bet you can't do that with your AS/400…. |
REST APIs Geospatial data |
| iSee Live from St Louis – Exception Handling in SQL During the Fall Navigate Conference Scott and Tim did a full length iSee session. Multiple topics were covered, this is the recording of one of these topics. Exceptions ?!? Not my code! Ha!!! This session will show some techniques on how to turn exceptions into meaningful messages. |
SQL PL |
| iSee Live from St Louis – Stored Procedures During the Fall Navigate Conference Scott and Tim did a full length iSee session. Multiple topics were covered, this is the recording of one of these topics. Stored Procedures – In this session we will find what stored procedures are on your system, see what ones are used the most, or the least, maybe not at all! |
Stored procedures |
| iSee SQL Building Blocks – Accessing Multiple Members in a Physical File Physical Files often have multiple members, how do you go about accessing information within each member? In this session we will give you a template. Scott and Tim will use Collection Services Files to programmatically find each member, and then integrate the contents. |
Physical File Members |
| iSee SQL Building Blocks – Dates and In-Line Functions In this session, Scott and Tim will look at several key building blocks for better SQL. We will review Dates! How do you move a decimal value to a Date value, what about a character value to a Date, we will look at some in-line functions that are key to easily being able to navigate the world of dates. |
Date manipulation |
| iSee Decimal Health Checker |
Data validation |
| iSee Un-Documented UDTFs User Defined Table Functions are known to many of us as UDTFs. The QSYS2 library is home to many, undocumented, UDTFs, which serve as the foundation of IBM i (SQL) Services views. In this iSee episode, you will learn about these behind-the-scenes hidden gems, and learn how to leverage them for a purpose. |
Table functions |
| iSee – Using Query Supervisor to Hold a Job We have talked about and shown Query Supervisor in the past. But we focused on Ending an SQL job that is violating the thresholds defined within the Query Supervisor. In this iSee we will review setting up the supervisor, and show you a few tricks that will give you the ability to just hold a job such that you can continue that workload at a later time. . |
Query Supervisor |
| iSee – How to Publish SQL Services Data into JSON We have shown you all sorts of ways to call SQL and get data returned. Nice right? What about taking that data output to the next level, why not return the data as a JSON document. This iSee will give you a generic way to call virtually any SQL Service and have the results published as a JSON document. |
JSON |
[{"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:
01 December 2023
UID
ibm16347648