FHG Archives - Page 68 of 144

Use the Dup Key in Subfiles

September 2, 2009 Hey, Ted

I hope you don’t mind another question about the “old” stuff. How do I program for the Dup key in a data entry subfile?

–David

This is a good technique to know, David, and it’s not hard to do.

Let’s start with a simple subfile of 10 records per page. There are three input fields: a date; a customer number; and an amount. Here’s the DDS:

A                                      DSPSIZ(24 80 *DS3)
A                                      INDARA
A          R SFL                       SFL
A            SDATE          6S 0I  8  6DUP(31)
A            SCUST          8A  I  8 16DUP(32)
A            SAMOUNT        7Y 3I  8 30DUP(33)
A          R CTL                       SFLCTL(SFL)
A                                      SFLSIZ(12)

…

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

…

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

…

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

…

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

…

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

…

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

…

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

…

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.

…

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

…

Previous Articles Next Articles

Search for: