• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Security Policies Vs. Security Procedures

    June 16, 2015 Patrick Botz

    It seems that many people don’t understand the difference between security policies and procedures. When I ask to see a customer’s security policy, if I get anything, it is usually documentation about how system security values should be set. Once in a while it contains a description about how certain tasks will be accomplished. For example, updating applications on the production system. While this kind of documentation is useful, it is not a security policy.

    In short, security policy identifies acceptable and/or unacceptable uses of various business assets. Importantly, security policy shouldn’t include descriptions of how to enforce, prevent, or

    …

    Read more
  • Two Ways To Rollup

    June 16, 2015 Ted Holt

    In Three Powerful SQL Words, I showed how to enhance summary queries with aggregate values over subsets of grouping columns. Today I continue that discussion by showing the two syntaxes of rollup and illustrating the difference between them.

    First, assume a table of accounting transactions.

    select department, account, amount
      from xacts2
     order by 1, 2
     
    DEPARTMENT  ACCOUNT     AMOUNT
         1          10      200.00
         1          10      250.00
         1          30      300.00
         2          10      125.00
         2          20      175.00
         2          20      225.00
    

    Here’s a typical summary query using the familiar GROUP BY clause.

    select department, account, sum(amount) as tamt
      from xacts2                                  
     group by department, account                  
     
    …

    Read more
  • EIM Identifier Naming

    June 2, 2015 Patrick Botz

    Enterprise Identity Mapping (EIM) is the technology that allows the IBM i to determine which user profile should be used to establish a connection for a person who has authenticated to an IBM i interface using non-IBM i credentials. EIM is easy to set up, but there is one thing you can do that will save you time and effort later.

    A quick overview of EIM will help explain the tip. EIM consists of three categories of information:

    1. EIM Identifiers representing people and entities (e.g., service userIDs) within the organization that have user IDs
    2. User Registry Definitions representing the various
    …

    Read more
  • Beware The Temporary Table

    June 2, 2015 Hey, Ted

    I am writing in response to your article Dynamic Lists in Static SQL Queries. At a recent NEUSG meeting, Tom McKinley of IBM warned us to avoid temporary tables, especially query chains of temporary tables, as they have no history for the optimizer. I like dynamic SQL. I would have left it alone.

    –Lynne

    I always enjoy hearing from Lynne because I know I will hear words of wisdom. Lynne raises a good point that I have intended for some time to address in this august publication.

    Kent Milligan, one of Tom McKinley’s colleagues at IBM’s DB2 for i

    …

    Read more
  • Paging Cursors And Position To

    June 2, 2015 Paul Tuohy

    Note: The code accompanying this article is available for download here.

    I had a reader email in relation to my article Paging Cursors. The article described a method for paging large lists using embedded SQL in RPG, and the reader was wondering if there was a way to position the list at a certain value. Of course there is!

    Please refer to the original article for all of the gruesome details but the basic concept is that you have a subprocedure that returns a “page” of rows from a result set. A page can be any number of

    …

    Read more
  • Prevent Overlapping In Range Tables

    May 19, 2015 Ted Holt

    In Joining On Ranges, I demonstrated that range tables are a practical replacement for attribute columns. As a rule, ranges should not overlap. (Perhaps there are exceptions.) Here’s why, and also what to do to prevent overlapping values.

    Overlapping ranges cause too many rows of the range table to join to a single row of another table, which in turn causes too many rows in the result set. For example, assume the following range table.

    FROMITEMNUMBER  THRUITEMNUMBER  ITEMCATEGORY
        134             134999           1
        1341            134199           1
        488             488999           2
        2               299999           3
    

    Item 134120 would join to the first two rows

    …

    Read more
  • Native Regular Expressions In DB2 For i 7.1 And 7.2

    May 19, 2015 Michael Sansoterra

    Blast 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

    …

    Read more
  • Job User Name And Current Job User

    May 19, 2015 Patrick Botz

    I see developers make one mistake way more often than any other. They assume that the job user name also represents the user profile under which a job is currently executing. This is, and always has been, an invalid assumption. The job user name only represents the userID under which the job was originally started. The user profile that a job is executing under at any given point in time (i.e., the current user) may or may not be the same as the job user. This may seem like a trivial and harmless mistake. But it often isn’t.

    In previous

    …

    Read more
  • Ruby And Existing Databases

    May 5, 2015 Aaron Bartell

    Recently I came across a scenario for a customer where I was asked, “How do we interact with our existing DDS-based, composite key, database tables?” That’s going to be a very common trait of most all IBM i shops using Ruby, and this article will cover some common situations you will come across as you use Ruby to interact with existing DB2 for i tables.

    First things first, let’s lay out two tables defined with DDS, as shown below. Note how the ORDDTL table has a composite key. The ibm_db Gem follows the ActiveRecord pattern and that pattern expects surrogate

    …

    Read more
  • Formatting Dates with SQL, Take 2

    May 5, 2015 Hey, Ted

    I am trying to use your FMTDATE SQL function but the system tells me it can’t find it. I know FMTDATE is there. Can you help?

    –Robert

    Note: The code accompanying this article is available for download here.

    I’ve received a compliment or two (at least) for the FMTDATE (format date) function I published back in 2009. (Can it really have been so long? How time flies!) I use it heavily, and I’m not the only one. If you’re not familiar with FMTDATE, please read Formatting Dates with SQL before continuing.

    To address Robert’s question, the reason the system

    …

    Read more

Previous Articles Next Articles

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • POWERUp 2025 –Your Source For IBM i 7.6 Information
  • Maxava Consulting Services Does More Than HA/DR Project Management – A Lot More
  • Guru: Creating An SQL Stored Procedure That Returns A Result Set
  • As I See It: At Any Cost
  • IBM i PTF Guide, Volume 27, Number 19
  • IBM Unveils Manzan, A New Open Source Event Monitor For IBM i
  • Say Goodbye To Downtime: Update Your Database Without Taking Your Business Offline
  • i-Rays Brings Observability To IBM i Performance Problems
  • Another Non-TR “Technology Refresh” Happens With IBM i TR6
  • IBM i PTF Guide, Volume 27, Number 18

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle