Sunday, 26 August 2012

#ibmi hints and tips #17: About reusing deleted records

I have been caught out so many times on this one ... If you create a physical file with CRTPF (whether using DDS or not), the default setting is REUSEDLT(*NO). If you create it using SQL, the default is REUSEDLT(*YES).

Tuesday, 14 August 2012

Interested in becoming a member of the IBM Electronic Support Client Advisory Panel?

I've been asked to publicise this IBM initiative for those with an interest in online technical support.

The Electronic Customer Support Advisory Panel meets on the 'phone every other month to review future plans for electronic support and to provide input about what they need from the Support web site.

This would take up about an hour every other month.

Participating gives members the opportunity to influence the enhancements and changes IBM makes to its electronic support; it also gives IBM a better understanding of what customers need and want in the next generation of support, and how IBM can address them.

If you're interested, I have more details and the necessary IBM contact information - you can get in touch with me at

Friday, 10 August 2012

#ibmi hints and tips #16: A small fact about CPYTOSTMF and CPYTOIMPF

I have only recently realised the simple fact that CPYTOSTMF and CPYFRMSTMF work with 'program described files' (as IBM i still, scarily, calls them), i.e. physical files created using CRTPF without DDS, while CPYTOIMPF and CPYFRMIMPF work with proper database files, i.e. physical files created using either DDS or a SQL CREATE TABLE. I'm sure you were all aware of this, but just in case ...

#ibmi geographical and national language handling overview

Or 'How to turn a dollar into a pound without even trying'.

I've been planning this one for years - needed a lot of research (most of what I thought I knew turned out to be wrong). I hope it makes sense, but as always please do let me know of any errors, omissions, or lack of clarity.

The document is at

Saturday, 4 August 2012

#ibmi hints and tips #15 : ODBC and CCSID 65535

Something I forgot to mention in my recent DB2-for-non-i-developers post is that database tables created using DDS are frequently left to the default CCSID of 65535, which means 'treat stored data as binary'.

This causes trouble when you access the database via ODBC (or indeed JDBC - see hint/tip #14), since alphanumeric fields are not transcoded, resulting in a lot of EBCDIC data that is meaningless in a PC context. (If you get a load of incomprehensible output with lots of @ signs in it, this is what's happened - the @ signs (hex 40) are spaces in EBCDIC.)

To resolve this, you need to tell the ODBC driver to ignore the 65535 setting and transcode the data. (It will use the CCSID on your user profile.) Here's the setting:

First post on Expert Integrated Systems blog

Back in June I attended an IBM PureSystems Social Media Residency at IBM Research Triangle Park, Raleigh, North Carolina. It was a fabulous experience. I learned an unbelievable amount, both about the PureSystems platforms and about the practicalities of marketing via social media, and I met a lot of really interesting (and nice) people. Anyway, one point of all this was to get the 15 residents blogging regularly about their particular angles on PureSystems. My first effort was published yesterday: More to come soon, hopefully.

Saturday, 28 July 2012

#ibmi hints and tips #14 : JDBC drivers

As an extension to my recent DB2 on IBM i post, here is a quick summary of the JDBC driver situation. As usual, please let me know if you spot any errors/omissions/lack of clarity.

Overall there are two options: DRDA/DB2 Connect, and the Java Toolbox.

DRDA/DB2 Connect JDBC drivers

Three different ones for different situations.

Where the Java is running on IBM i itself

Native DRDA JDBC driver:
  • no need to put a jar or zip file in your classpath
  • jdbc:db2:(database-name-from-WRKRDBDIRE)
Very fast, resilient and secure, as just mentioned no need to find jar or add to classpath, but has some JDBC support limitations and is not IBM strategic.

If database is *LOCAL (and potentially if database is on another IBM i system, depending on WRKRDBDIRE configuration) there is no need to specify userid/password.

Where the Java is running on other platforms (chargeable)

If DB2 Connect client installed

  • [note subtle distinction between this and the driver class name mentioned above]
  • jdbc:db2:(name-from-DB2-Configuration-Assistant)


  • db2jcc.jar plus separate licence jar
  • jdbc:db2://(IBM-i-hostname):50000/(database-name-from-WRKRDBDIRE)

Wherever the Java is running

Served on IBM i by the *DDM TCP/IP server, unless *LOCAL (and unless WRKRDBDIRE is configured to use SNA).

Not really recommended (due to complexity, lack of IBM investment, and DB2 Connect cost) unless the application needs to be portable between DB2 platforms.

IBM i Toolbox for Java JDBC driver

Only one for all situations.
  • jt400.jar
  • [sorry, it really is still called that]
  • jdbc:as400:(IBM-i-host-name)
No installed client required, can run on any platform, fewer JDBC support limitations than the DRDA driver, IBM strategic, free of charge.

Needs adding to classpath but otherwise very simple.

If running on IBM i itself, use /QIBM/ProdData/java400/jt400ntv.jar version, or optimise jt400.jar to level 40 with CRTJVAPGM.

Turn on the 'translate binary' option if you have tables with CCSID 65535 (as is common - next blog post will probably be on CCSIDs and code pages, incidentally).

Served on IBM i by the *DATABASE host server.

Recommended (much as I like using DRDA in other situations).

Saturday, 14 July 2012

Introduction to #ibmi Spooling, Printing, and Electronic Output

I've finally finished something I've been working on for a while - a short article introducing the various free-of-charge and chargeable IBM i spooling, printing, and electronic output capabilities. As it has quite a few pictures, I've uploaded it as a PDF, here. If you spot any errors, omissions, or lack of clarity, please post a comment.

Sunday, 24 June 2012

An attempt at explaining DB2 on #ibmi to the non-i developer

DB2 for i as a relational database:

  • The functionality is right up there with the competition.
  • DB2 for i looks very similar to other DB2’s from the coder’s perspective, but do not be fooled.
  • Management and maintenance are specific to IBM i, and are far simpler than on other platforms, because the database takes full advantage of the IBM i architecture.
The fundamental differences from other database platforms are cultural and historical:
  • IBM i shops have traditionally (and probably, with hindsight, foolishly) had no DBA role, and thus the structure, security, performance, and integrity of the database are not owned as they would be on other platforms.
  • SQL is extensively used in application coding (whether RPG, Cobol, or Java), but some sites still use native externally described file functionality from RPG or Cobol. Even where coding uses SQL, the database itself is frequently still created and maintained using the alternative Data Description Specifications (DDS) approach.
  • Data types used may well be limited to those that were supported by RPG on the System/38 (e.g. no DATEs).
  • Referential integrity tends to be built into the application.
  • Primary keys are not seen (or, really, implemented) as conceptually different from other unique keys.
  • Many applications do not use transaction boundaries (commitment control).
  • Database files (tables/views/indexes) are scattered across the system, frequently sharing libraries (schemas) with other objects.
  • Management and maintenance is typically all done from a green screen.

DB2 within the IBM i environment:

  • The database is built in to the operating system (and therefore, effectively, free of charge).
  • Database components (physical and logical files, i.e. tables, views and indexes) can exist in any library (schema); they can be managed and accessed via SQL or via traditional IBM i commands and programming techniques, interchangeably.
  • Columns are traditionally referred to as fields, and rows as records.
  • Table and column names are frequently short (for use in RPG) and/or meaningless (because automatically generated), and the provided ability to alias them to longer, meaningful values is very often ignored. Metadata support similar to that of DB2 on other platforms is present, but is not widely used. However, where DDS is the chosen database maintenance mechanism, there is usually extensive IBM i native metadata present (50-character descriptive text, up to 3 column headings); the system-wide catalog in library QSYS2 (SYSTABLES, SYSCOLUMNS, etc.) exposes this native metadata together with whatever non-IBM i-specific metadata may be present.
  • A stored procedure, function, or trigger handler can be written  in SQL or in any programming language supported on IBM i, including Java.
  • There is a very powerful SQL optimizer supported by strong performance analysis tools (part of the free-of-charge IBM i Navigator).
Some additional functionality:
  • MySQL can be used as a front end to DB2 on i.
  • Omnifind provides a system-wide database search facility.
  • The database has XML input/output functionality (see recent relevant blog post).
  • Encoded Vector Indexes (EVIs) are supported for tables with low cardinality.
Distributed database processing:
  • The native connectivity of the database, as with DB2 on any platform, is DRDA (Distributed Relational Database Architecture, aka DB2 Connect).
  • However, DB2 on i's standard ODBC/JDBC support uses the IBM i-specific database host server and has nothing at all to do with DRDA.
  • Except between IBM i systems, DB2 Connect connectivity to DB2 on i is optional (i.e. costs money), rarely used, and poorly understood. Other DB2’s and their DBAs use DB2 Connect as standard, so this is often a barrier.
  • DRDA between IBM i systems is easy to implement, free of charge, and supports two-phase commit (Distributed Unit of Work). The corresponding non-SQL  functionality, Distributed Data Management (DDM), is also highly functional and widely used.
  • The listeners for both DRDA and DDM are started using STRTCPSVR *DDM.
  • DRDA is configured using WRKRDBDIRE.

Wednesday, 20 June 2012

IBM i hints and tips #5 to #12

Don't want to lose these in the depths of Twitter.

Assorted  hints and tips #5 : how to improve usability of CL commands very easily - great tool at 

Assorted  hints and tips #6 : RTVQMQRY ALWQRYDFN(*YES) allows retrieval of the equivalent SQL from a Query definition (*QRYDFN object)

Assorted  hints and tips #7: IBM i Access value add: socket level ping: cwbping any-hostname-or-ip-address /port:any-port-you-like

Assorted  hints and tips #8 : Limiting the size of an SQL result set : FETCH FIRST n ROWS ONLY

Assorted  hints and tips #9 : Want to know the total size of a library? DSPLIB to a spool file shows it at the bottom

Assorted  hints and tips #10: Hints for creating a usable AFP overlay from a letterhead stored in Microsoft Word: 

Assorted  hints and tips #11: To get a list of IFS items: RTVDIRINF. 2 linked outfiles:…D hierarchy,…O list of stream files/directories

 hints & tips #12: Spool files for jobs that change user belong to the current user, not to the job. OVRPRTF SPLFOWN(*JOB) changes this

Ways of handling XML and web services calls from RPG #ibmi

Someone asked me an IBM i/RPG/XML question yesterday and I couldn't find a simple summary of the options available, so I put one together - if I missed a suitable link, or if there are errors/omissions in this, please comment.

This list excludes the various third party options available.

Low-level reading and writing of XML direct from RPG code:

  • Parse: XML-INTO and XML-SAX RPG opcodes (V5R4 onwards)
  • Generate: Roll your own (not difficult, but not standards-based and not very maintainable)
  • Parse and Generate: XML Toolkit, 5733-XT1 (V5R4), 5733-XT2 (V6R1 onwards): usable from any ILE language, but complex to use direct; a ‘wrapper’ called XML4PR is provided to simplify usage from RPG
  • Best way of all, potentially: Open Access handlers, once these become available (apparently under development by IBM STG Lab Services) – have the potential to hide all XML parse and generate complexities from the programmer – I imagine they would be chargeable though (V7R1 onwards)

Consuming web services direct from RPG code:

  • Web Services Client for ILE: provides a way (WSDL2WS tool) of generating a ‘proxy’ C or C++ service program, although this is complex to invoke from RPG – recently simplified by incorporation of WSDL2RPG tool, which generates an RPG ‘proxy’ instead


  • Use your chosen open source Java XML and web services client functionality, invoking your Java classes from RPG
    • Pro:
      • This may be the simplest/most maintainable way to do it, especially if you make use of the Java Toolbox record access classes to handle external description
    • Con:
      • Configuration management of the necessary open source jar files may be an issue (you are also likely to duplicate functionality that is now built in to IBM i, e.g. Apache Axis open source web services layer)
      • Getting the performance right may take considerable effort the first time
      • Change management/source code control may be an issue unless you have other non-J2EE Java code in production

XML-based data storage and retrieval (XML SQL datatype) from RPG code:

Sunday, 20 May 2012

Two operating systems, and rubbish customer service from a 'phone manufacturer

Two Saturdays ago I woke to find my Windows Phone (Samsung Omnia 7) was on a major go-slow - it was fine for anything not involving the built-in secure SD card, but I couldn't even do a hard reset. The (always excellent) Bath T-Mobile shop agreed with my provisional diagnosis and sent the 'phone back to Samsung for repair, providing me temporarily with a little Nokia 'phone which proved surprisingly full-functional (if you can live without 3G and with a storage capacity of about 50MB, that is).

In the meantime I decided that it was time I stopped evangelising Windows Phone from a position of knowing nothing about either iOS or Android, so I bought myself a 7" Ice Cream Sandwich tablet (Neuropad). An interesting and overall positive experience.


  • Good and clearly responsive supplier (AndroidSlates via Amazon)
  • Brilliant battery life - I never turn it off
  • Pretty intuitive (though it took me a while to work out how to display the app list - initially, I was faced with what seemed a completely blank screen)
  • Clearly excellent selection of apps (though I have installed no functionality that doesn't exist for WP7, except BBC iPlayer)
  • Perfect for productive train travel - handle emails, read PDFs without straining eyes
  • Good wifi reception (could use a 3G dongle if I needed to)
  • File Explorer, and ability to map tablet as network drive via USB
  • Good support for Windows Live and SkyDrive, once you've found the apps (why call the WL app Hotmail?)
  • Nice implementation of OneNote Mobile (free of charge, and with far faster sync than on WP7)
  • Respectable Office document editor (Documents to Go) for under £10 (though the PowerPoint element is feeble)
  • Flash (iPlayer app works perfectly ... but see below for the other side of this story)
  • 'Running apps' list with 'close' function (I see now why the relevant request has so many votes on


  • Can't launch attachments from OneNote pages (now using SkyDrive direct instead, but it's not as seamless)
  • Billing complications - Amazon option doesn't work in the UK as far as I can see; Google Play allows entry of an Amex card, but then doesn't let you use it for Android app payments, without explanation
  • Can't see the back / home / menu buttons in the dark (surprisingly annoying)
  • Even on max volume, Neuropad is pretty quiet
  • Non-integrated email (why should I care whether it's Gmail or not?)
  • Contact handling is not at all intuitive - took me a while to work out where it was finding contacts, and even longer to identify/install a decent app to manage them with - no built-in contact management app at all (even the little Nokia mentioned above did better than that)
  • Apps fairly frequently stop responding - may be related to trying to do too much with small memory in Neuropad (fair enough) and/or to bad wifi reception
  • Flash (even worse than on Windows, e.g. Cricinfo website is unusable)

Lots more to do - install and learn Swype keyboard, find better browser, etc. - but overall v pleased with it. However I have seen nothing to make me think I would be better off with an Android 'phone.

Meanwhile after only 10 days the Windows Phone was ready for collection.

Samsung informed us in writing that they had tested everything in sight and 'updated the software to the latest level'. We were puzzled, given the fact that there'd fairly obviously been a hardware fault.

On investigating the 'phone and switching it on, it became clear
a) that this was not my 'phone handset but a rather battered looking replacement they hadn't bothered to mention (so my carefully preserved Zune backup would be useless), and (worse)
b) that, far from being on the 'latest level', it was on the very earliest shipped OS level (7004).
I think the politest way of describing Samsung's actions here is 'economical with the truth'. I can't see that they give two hoots about their customers. I'm as angry about this as I have been with a vendor for a long time - it feels like they're taking both the Bath T-Mobile shop and me for fools.

Anyway three hours, much swearing at Zune, a PC restart, and 4 updates later, I was ready to re-install my apps and get things back to normal.

In summary: much as I love my Omnia, my next 'phone will probably be a Nokia Lumia 900; and it won't be running Android.

Tuesday, 15 May 2012

The #ibmi timeline up to date

What an amazing story ...

System/3 1969-1975 Product of General Systems Division, formed as result of anti-trust legislation. Low-end batch system replacement for unit-record equipment. Used RPG programming language. Disk and terminals soon added. 28-instruction CPU. Over 25,000 sold.
System/32 1975-1977 System/3 follow-on.
System/34 1977-1981 System/32 follow-on. Ran SSP OS.
System/36 1981-1986 System/34 follow-on. Ran SSP OS.
System/38 1978-1988 Pioneering design, revolutionary architecture system, continued in AS/400, iSeries, System i5, IBM i to date, provides complete insulation for user applications against technology change. Also, first system line to include in-built relational database system. Ran CPF OS.
AS/400 1988-2000 Repackaged and improved S/38, hugely successful in medium business markets, using S/38 architecture. By mid-late 1990s, became open, e-business capable server. 200,000th AS/400 sold by 1992. OS/400 OS. From 1995 based on RISC chip (PowerPC).
iSeries 2000-2002 Repackaged and re-branded AS/400, under eServer strategic initiative.
New iSeries 2003-2004 Radically reshaped and extended, repackaged and re-priced iSeries, repositioned as "On Demand" e-business hub platform consolidating Windows/Intel, Linux, AIX, Java and Domino (as well as traditional OS/400) workloads. Over $500M IBM investment.
System i5 2004-2007 Technology convergence with pSeries, including AIX in a partition. Operating system now called i5/OS.
IBM i on PowerSystems hardware 2007- No distinction between i and p hardware - all just Power Systems. Operating system now called IBM i.
IBM i on PureSystems platforms 2012- Expert integrated systems: ranges of infrastructure and application platforms with full support for IBM i workloads.

Sunday, 6 May 2012

Assorted #ibmi hints and tips #4 : Creating an output table directly from a SQL SELECT

Another only recently learned but mega useful SQL trick ...

create table schema/table as (select statement) with [no] data

create table MYLIB/MYOUTPUT as (SELECT COLUMN1, COLUMN2, COLUMN3 FROM ...) with data
creates physical file MYOUTPUT in MYLIB with fields COLUMN1, COLUMN2, COLUMN3  and populates it with the SELECT result set, while
create table MYLIB/MYOUTPUT as (SELECT  COLUMN1, COLUMN2, COLUMN3 FROM ...) with no data
creates empty physical file MYOUTPUT in MYLIB with fields COLUMN1, COLUMN2, COLUMN3, and
create table MYLIB/MYOUTPUT as (SELECT  * FROM MYLIB/MYINPUT) with data
does the equivalent of

It's worth pointing out that the columns of the SELECT have to have valid field names, thus
create table MYLIB/MYOUTPUT as (SELECT COUNT(*) FROM ...) with data
will not work, while
create table MYLIB/MYOUTPUT as (SELECT COUNT(*) AS MYCOUNT FROM ...) with data
is fine.

The newly announced RUNSQL CL command will make the above even more useful, as it will make it immediately accessible from CL.

Friday, 4 May 2012

Assorted #ibmi hints and tips #3 : Handling multi-member physical files from SQL

Here's another useful possibility I hadn't spotted until recently.
For SQL access to a non-default member, use an alias, e.g.:

By the way, if you missed hint #2:
Assorted IBM i hints and tips #2: Making physical file DDS changes faster, easier, and less risky  Nice article at 

Saturday, 21 April 2012

Assorted IBM i hints and tips #1: CREATE TABLE authority setting #ibmi

This one caught me out recently:
If you do an SQL CREATE TABLE using JDBC with the default *SQL ('.' separator) naming convention, the system always sets AUT(*EXCLUDE); the normal object creation default of AUT(*LIBCRTAUT) is ignored.
To force AUT(*LIBCRTAUT), use the *SYSTEM ('/' separator) naming convention, by adding ;naming=system to the end of the JDBC URL.

Thursday, 19 April 2012

i-Community initiative for #ibmi users in the UK

A new initiative for UK IBM i users is now up and running.

i-Community is aimed at maximising the benefit of your IBM i investment, by sharing experiences and concerns with your peers, and through face-to-face meetings led by top speakers.

i-Community is a non-commercial environment; it is not aimed at marketing any product or service, rather it is a non-profit-making peer group facilitated by its sponsors, five IBM Business Partners (Applied Technologies, Binary, CSI, MITL, iPerimeter). Numbers of session attendees are kept low enough to permit a high level of interaction. The full annual membership subscription covers all costs for one delegate at our spring UK meeting and on the autumn visit to the IBM Rochester Laboratories, including the return flight to Minneapolis, with a small uplift to cover overheads. A UK-only subscription is also available.

i-Community sessions will be realistic, example driven, and will typically look at the subject area from both a technical and a business viewpoint. The topics covered are chosen by the community to cover IBM i infrastructure and development matters, but also non-IBM i-specific subjects of general interest to members, such as SOA, compliance or unified communications. Topics might occasionally be led by groups of members, with an IBM representative present to handle questions. It is the organisation that is the member, not any individual, so attendees can be chosen to suit the topic.

Each organisation signs a Confidential Disclosure Agreement with IBM Rochester, providing members with access to the futures information they need for budgeting.

More details are at, and the inaugural meeting is on Wednesday 16 May at Wroxall Abbey near Warwick, with dinner/overnight stay on Tuesday 15 May – here is the agenda:
IBM POWER i and i/OS Update: Nigel Adams - IBM UK
PowerHA and i/OS: Stu Stebbings - IBM UK
VIOS and POWER i / AIX workloads: Tom Watts - IBM UK
Rational Development Tools for Power i/OS: Pat Flisher - IBM UK
Enterprise Service Bus - WebSphere Messaging: Dave Willmott – CSI
The charges are:
Membership to include UK event only: £250 plus VAT (additional attendee £175 plus VAT)
Membership to include both events in 2012: £1,850 plus VAT
Do let me know (01225 436302 or if you want to know more about it, or if you are at all interested in getting involved.