Michael Sansoterra
-
SQL Paging With Limit And Offset In DB2 For i
January 12, 2016 Michael Sansoterra
… Read moreLIMIT and OFFSET are two new query options that will please every developer who builds user interfaces (UI) involving potentially large result sets. Handling UI queries that return a large number of rows can be annoying as they may take too long to process, create heavy network traffic, and require a web or desktop client to cache the result set. Further, it’s unlikely a user will review all the rows! But what if DB2 returns only what is needed by giving back one slice of the entire result set on demand?
This is where the new LIMIT and OFFSET features
-
Extracting Sample Data From A DB2 for i Table
November 3, 2015 Michael Sansoterra
… Read moreThe need to retrieve a sample or subset of data from a database is a relatively common (and somewhat annoying) task. For example, DB2 for i developers often want to extract data and create a scaled down copy of a library (a.k.a. schema) to develop with. Additionally, sometimes code testing can be done using random data, such as creating a battery of orders to process with varying customers, order terms, etc. This tip won’t attempt to tackle the full breadth of either of these scenarios. However, the aim is to show how some simple queries can be used to create
-
Questions About AES Encryption In RPG, DB2 for i, And The Web
October 27, 2015 Michael Sansoterra
… Read moreHey, FHG:
We have never used encryption. Our new manager wants us to start using it on our IBM i 7.1 system. (For instance, we store passwords for our website members). I use the ENCRYPT_AES and DECRYPT_CHAR SQL functions in my RPG programs. However, with respect to AES encryption, our PHP web programmer is asking about how to generate an initialization vector and then save it later for decryption. Can you point us in the right direction? Finally, our manager doesn’t like the fact that the encryption key is in plain text in RPG source code. What are your
-
DB2 for i 7.1 TR10 and i 7.2 TR2 Features, Part 2
October 20, 2015 Michael Sansoterra
… Read moreContinuing on from Part 1, this tip covers some new features recently made available in DB2 for i.
SQL Function Parameter Limit
User-defined functions capabilities in DB2 for i have received a huge boost in that the parameter list limit for a scalar function has jumped from 90 parameters to a whopping 1024 parameters. Likewise, the combined input parameter and return columns limit for a table function has increased from 125 to 1025.
Off the top of my head I can’t think of an example when I would want to pass more than 90 parameters into a scalar function.
-
DB2 for i 7.1 TR10 and i 7.2 TR2 Features, Part 1
October 6, 2015 Michael Sansoterra
… Read moreThis is just another great DB2 feature tip, calling attention to some useful features available in the recent releases of DB2 for i. To determine whether you can use these recent features, check with your IBM i administrator to make sure your database group PTF levels are up to date. For IBM i 7.1 the database group PTF level should be at least 34, and for 7.2 the database group PTF level should be at least 5.
VARCHAR_FORMAT_BINARY and VARBINARY_FORMAT
Two new functions were added to the DB2 for i built-in function arsenal that will assist developers with the annoying
-
RCAC In DB2 For i, Part 3: Advanced Topics
September 15, 2015 Michael Sansoterra
… Read moreIn Part 1 and Part 2 of this RCAC series, I covered row and column access control (RCAC) row permissions and column masks, and demonstrated how they can be used to add an additional layer of security to your DB2 for i database without necessarily having to change legacy applications. This tip will explore the RCAC topic a little further by discussing a few advanced concepts that may affect your implementation of RCAC.
Bypassing RCAC With SET SESSION AUTHORIZATION
When the SET SESSION AUTHORIZATION statement is executed, a SQL database connection is “impersonated” to run under the identity of another
-
RCAC in DB2 For i, Part 2: Column Masks
September 1, 2015 Michael Sansoterra
… Read moreIn part 1 of this series, I discussed the row permissions portion of the new row and column access control (RCAC) security feature that was introduced in DB2 for i 7.2. In this tip, I will demonstrate how to use RCAC to hide sensitive information without hiding entire rows from a user.
To sum up the last tip, RCAC is beneficial for protecting an integral business asset: data. Row permissions allow a database administrator to limit the rows users can view or modify in a table by defining access rules. Permissions to access rows are generally based on user profile
-
RCAC in DB2 for i, Part 1: Row Access Permissions
August 18, 2015 Michael Sansoterra
… Read moreIn a world of hackers, every data asset is vulnerable to theft or tampering. Protecting data can be costly, yet being hacked is even costlier. Enter IBM‘s row and column access control. RCAC is a big deal as businesses are looking for effective tools to control security, in particular, database security.
RCAC allows database security administrators to tighten the reigns on who can view data in the database. Even better, many of these security measures can be implemented without changing applications. Versions of DB2, Postgre SQL, Oracle, SQL Server (currently in preview with Azure v12 and SQL Server 2016),
-
Object Dependency Tracking In DB2 For i
July 21, 2015 Michael Sansoterra
… Read moreThe DB2 for i catalog tables and views present a plethora of valuable information about the various SQL objects defined on your system and their relationship to one another. In this tip, I discuss the dependency tracking views. If you ever wanted to make changes to one of your application’s primary SQL objects (such as a table, view or procedure) but feared the unknown (what related objects are affected?), then it’s time to investigate these special catalog views.
What Are The Dependency Catalog Views?
The “dependency” catalog views under consideration are:
SYSROUTINEDEP–Records the objects used by stored procedures and
-
Native Regular Expressions In DB2 For i 7.1 And 7.2
May 19, 2015 Michael Sansoterra
… Read moreBlast it! Another suite of custom code I have written and used over the years has recently been deprecated (or partially deprecated) by IBM. The good news is that regular expressions (abbreviated RegEx) are now a native part of DB2 featuring one new predicate (REGEXP_LIKE) and four new scalar functions: REGEXP_COUNT, REGEXP_INSTR, REGEXP_SUBSTR, and REGEXP_REPLACE.
For IBM i 7.1, TR9 must be installed and for IBM i 7.2, TR1 must be installed. The new Regular Expression Functions Require licensed product #39 5770-SS1 International Components for Unicode to be installed on both IBM i 7.1 and 7.2. (This product is