• Guru: Another Red Flag – Joining On Expressions

  August 30, 2021 Ted Holt

  One would think that a given datum, for example a sales order number, would be defined identically in the various database tables in which it is found within an organization, but one might be wrong. I have on many occasions faced the challenge of joining two or more tables on unmatched data types.

  But it gets worse than that. Sometimes joins involve expressions, which may consist of mathematical operations and/or invocations of functions, both intrinsic and user-written. As with the word DISTINCT in a SELECT, the presence of expression in a join sends up a little red flag that …

  Read more

• Guru: Set Beats A Loop

  August 23, 2021 Ted Holt

  Hey, Ted:

  I’m sending you some RPG source code that I put into one of our inquiry programs and would like to have your insight. Instead of using the SETLL, DOW and READE opcodes to loop through a group of selected records, I used the SQL SET statement. This seems to me much easier for someone else to understand and follow. Is this good practice?

  — Mike

  I was glad to see Mike’s email in my inbox, as always. He and I met in person at the RPG & DB2 Summit a few years ago, and I’ve enjoyed getting to …

  Read more

• Guru: Odds and Ends

  August 16, 2021 Ted Holt

  I really appreciate the comments that readers add to the end of articles or send to me in email. The more you share, the more all of us are better off. I know that you don’t have time to go back through articles we have published to see if anyone has commented, so this week I do that for you. Please keep the feedback coming!

  Several people posted responses to Paul Tuohy’s article Getting Meaningful Audit Information from a Journal. You can read them for yourself, but I wanted to thank Emanuele, who mentioned a tool that is similar …

  Read more

• Guru: The Deception of Fractional Labeled Durations

  August 9, 2021 Ted Holt

  Hey, Ted:

  We measure certain processes in tenths of an hour. For whatever reason, we cannot make date/time arithmetic work properly when dealing with this data. There must be something that we don’t understand — what we’re doing seems simple and straightforward. Can you help?

  — One Confused Reader

  What this reader wants to do makes perfect sense. He wants to take a value like 8:00 AM, add a fractional number of hours to it, let’s say 1.1, and come up with 9:06 AM. Let’s try an example.

  declare global temporary table StampData
   ( Stamp   timestamp );
      
  insert into session.StampData 

  …

  Read more

• Guru: Elapsed Time For Human Beings

  August 2, 2021 Ted Holt

  Quick! Think of a weird animal! Perhaps you thought of the platypus, a duck-billed mammal that lays eggs. Maybe Dr. Doolittle’s pushmi-pullyu jumped to mind. Maybe you thought of a politician or a musician or your next-door neighbor. I know a weird animal that you probably didn’t think of.

  The weird animal I have in mind is called the duration. This animal is found in SQL queries. There are three species: timestamp, date, and time. Today I write about the challenges of interacting with this strange entity. I’ll use the timestamp duration as an example. Interacting with date …

  Read more

• Guru: Getting Meaningful Audit Information from a Journal

  July 26, 2021 Paul Tuohy

  Journaling is an invaluable tool that is used for data recovery, data replication, commitment control and, of course, auditing. But getting at the audit information in an easy-to-use manner can be cumbersome. In this article I want to introduce you to a stored procedure that will create an audit table for any table/physical file and a corresponding view that can be used for easy auditing of changes.

  For example, if I am auditing the EMPLOYEE table (I will be using the EMPLOYEE table in the standard Db2 Sample Database) for a change to the SALARY column, I would use the …

  Read more

• Guru: One-Shot Requests and Quoted Column Names

  July 19, 2021 Ted Holt

  If I had a dollar for every time someone has asked me to query data for them over the years, I could have retired already. (I did not say I would have, but that I could have.) It’s nice when the users can retrieve the information they need to do their jobs without help from IT, but when a request is too complex for them, I’m always glad to help.

  I’ve used many tools over the years for one-shot requests for raw data. These days my tool of choice is the Run SQL Scripts tool, which is part of IBM …

  Read more

• Guru: Web Services, DATA-INTO and DATA-GEN, Part 3

  July 12, 2021 Jon Paris

  In this tip I’m going to discuss some of the options used with DATA-GEN and DATA-INTO to deal with the fact that element names in JSON and XML frequently contain characters that are not legal in RPG names.

  This is important because RPG’s -INTO and -GEN operations rely on names to map elements. So if the document we are processing uses names that are not legal in RPG how can we handle that? As you will see, when dealing with -INTO operations they are quite easily handled. DATA-GEN, on the other hand, presents a different problem and we will get …

  Read more

• Guru: Understanding Database Performance Using The Performance Data Investigator, Part 2

  June 28, 2021 Dawn May

  This is the second in a two-part series on how you can use the Performance Data Investigator (PDI) to investigate performance of Db2 for i. While the SQL Performance Center in Access Client Solutions is more commonly known than PDI, using both tools is a good way to analyze database performance.

  In Part 1, I reviewed the Database content package, focusing on the charts for I/O reads and writes, SQL CPU utilization, and database locks. In this tech tip, I continue by reviewing the charts related to SQL Cursor and Native DB Opens as well as the SQL Performance …

  Read more

• Guru: Understanding Database Performance Using The Performance Data Investigator, Part 1

  June 21, 2021 Dawn May

  This is a two-part series on how to use the Performance Data Investigator (PDI) for investigating performance of Db2 for i. While the SQL Performance Center in Access Client Solutions is the tool of choice for Db2 performance analysis, there is valuable information that you can get from using PDI. PDI is often a useful starting point to help guide you in the correct direction when trying to identify performance issues. In my experience, PDI is under-utilized for Db2 investigation. For those not familiar with PDI, you may want to read the article, Getting Started with the Performance Data Investigator …

  Read more

Previous Articles Next Articles