• An Overview of User-Defined Types in DB2 for i

  September 2, 2009 Michael Sansoterra

  UDTs or User-Defined Types (also known as distinct types) allow SQL developers to implement their own data types. For example, if a database contains many tables that each have one or more columns representing a general ledger account number defined as VARCHAR(30), a suitable UDT can be defined as follows: Create Type My_Schema/GL_ACCOUNT_NUMBER As VarChar(30) CCSID 37 With Comparisons In DB2 for i, implementing UDTs is currently limited to deriving a new data type from an existing DB2 built-in data type. This tip will briefly explore UDTs, how they can be used in modern databases, potential pitfalls, and how UDTs

  …

  Read more

• Admin Alert: The Road to Live CBU Fail Over, Part 1

  September 2, 2009 Joe Hertvik

  One of the companies I work with performed its first live Capacity BackUp (CBU) switch test last month, where they switched over and used their CBU system as their live production system for several days. In the next few issues, I’ll use their experience in prepping for a live switch as a possible guide for others trying to ensure that their CBU can substitute for a live system. CBU 101: Understanding the CBU A CBU is an i, System i, or iSeries machine that is an exact duplicate of a live production system. CBUs generally contain the same amount of

  …

  Read more

• Validate DBCS-Open Data

  August 19, 2009 Michael Sansoterra

  The DBCS open data type is used in many legacy applications as a way to support storage of either single byte character set (SBCS) data, double byte character set (DBCS) data, or even data from both character sets within a single data column. Defined with a data type of O within DDS specs, this data type carries an inherent agnostic coded character set identifier (CCSID) of 65535. The DBCS open data type operates by storing single byte characters, as traditional single byte character columns do. However, when a DBCS open column stores double byte characters, it uses special embedded escape

  …

  Read more

• Formatting Dates with SQL

  August 19, 2009 Ted Holt

  Note: The code accompanying this article is available for download here. The famous ERP package with which I work at my day job stores dates in seven-digit packed decimal fields in CYYMMDD format. Reformatting these dates into something readable in an SQL query is challenging, to put it mildly. To complicate matters, our homegrown systems have dates in YYYYMMDD formats, and outfiles of CL commands have dates in MMDDYY format. I finally got tired of converting and substringing and concatenating, and decided to write a general SQL function to convert dates. I hastily threw it together, but it seems

  …

  Read more

• Admin Alert: Correcting and Expanding the Program to Change User Passwords on the Fly

  August 19, 2009 Joe Hertvik

  Last week, I published code that allows authorized users to change user profile passwords with a simple call command. However, several readers emailed me about a mistake that will stop the code from compiling. This week, I’m publishing a correction to the code, as well as a new function for preventing the code from changing passwords for security officers, security administrators, and users with all object access. Correction: Updated Password Program Change Code The PSSWRDCHG code I published last issue allows selected users without security administrator authority the ability to change a user profile password or to disable a user

  …

  Read more

• Designing DB2 for i Stored Procedures for Simulated Array Handling

  August 5, 2009 Hey, Mike

  I enjoyed your article on using .NET to access DB2 on the iSeries. I am trying to extend the functionality of our ERP software with VB.NET 2005/2008. The data is on the iSeries. I plan to use DB2 stored procedures for data retrieval. One of my main stumbling blocks is how to pass a variable number of parameters to a stored procedure. For instance, let’s say a project manager is responsible for 10 projects that have specific project numbers. He can query on one project, all 10 projects, or any variable number of projects. How does one design a stored

  …

  Read more

• Using Free-Format Calcs with Cycle Programs

  August 5, 2009 Hey, Ted

  I use free-format calculations for new development and for existing cycle-less RPG programs. However, I have responsibility for some old programs that use the RPG cycle. Is it possible for me to use free-format calcs in those programs? –Bill Yes, Bill. In the detail calculations (calcs with no level indicator in columns 7 and 8), use the /FREE and /END-FREE directives as you normally would. In the following example, I have added free-format calcs that are to be executed at L1 detail time (before a new control group) and at detail time (for each input record). C L1 MOVE *ZERO

  …

  Read more

• Admin Alert: Changing User Passwords on the Fly

  August 5, 2009 Joe Hertvik

  It’s always helpful to have some CL code handy for various system functions that i5/OS administrators may need to perform. This week, I’m presenting one of my favorite administrative CL programs: code to change passwords and disable user profiles through a program call. Feel free to examine this code and use it in your shop whenever you need to deputize users without security administrator authority to change passwords. How This Code Began Every once in awhile, I need to allow selected users without security administrator authority the ability to change a user profile password or to disable a user profile.

  …

  Read more

• Circumventing Two Limitations of CPYTOIMPF

  July 22, 2009 Ted Holt

  Dear Intelligent Power-on-i Professional: I deem it grand of IBM to give us great tools that relieve us of the need to write programs to accomplish mundane, quotidian tasks. The Copy to Import File (CPYTOIMPF) command is such an animal. However, nothing’s perfect on this planet, and CPYTOIMPF is no exception. Here are two annoyances or problems (depending on your point of view), and how to get around them. Strip Out Unwanted Blanks I’m using CPYTOIMPF to build a CSV file. CPYTOIMPF leaves lots of blank space in the stream file. Is there any way to tell CPYTOIMPF that I

  …

  Read more

• Comparing RPG and SQL Functionality

  July 22, 2009 Hey, Mike

  Is there a book on how RPG operation codes and built-in functions translate to SQL? I’m trying to leverage my RPG coding skills to write logic in a DB2 stored procedure. That is, I know exactly what to do in RPG, but I don’t know the SQL equivalent. To determine the length of a city name, I would use RPG code like this: Eval CityLength = %Len(%Trim(City)) How would I do the same in a stored procedure SET statement? I have used the following to find a field length, but my field is an input parameter to the stored procedure.

  …

  Read more

Previous Articles Next Articles