#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 http://www.notamos.co.uk/General/CodePagesEtc.pdf.

#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: http://expertintegratedsystemsblog.com/index.php/2012/08/ibm-puresystems-and-isvs-a-valuable-relationship-2/?sf5396958=1. More to come soon, hopefully.

#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
• com.ibm.db2.jdbc.app.DB2Driver
• 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

• db2java.zip
• COM.ibm.db2.jdbc.app.DB2Driver [note subtle distinction between this and the driver class name mentioned above]
• jdbc:db2:(name-from-DB2-Configuration-Assistant)

Otherwise

• db2jcc.jar plus separate licence jar
• com.ibm.db2.jcc.DB2Driver
• 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
• com.ibm.as400.access.AS400JDBCDriver [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).

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.

#ibmi hints and tips #13: PASE versus QShell

Great article by Alan Seiden at http://www.mcpressonline.com/tips-techniques/programming/techtip-qshell-vs-pase.html