Saturday 21 October 2017

Hints and Tips update (aka, SQL on #ibmi and #sqlserver: divided by a common language)

Decided to take a nice 'split string' table function I'd borrowed from some kind person on the internet for the Coordinate My Care data warehouse (SQL Server), and use it as a basis for a similar function to split multi-value attributes on IBM i, e.g. list of special authorities on a user profile. Cue a frustrating, if eventually successful, afternoon.

I really don't like #ibmi table functions: for starters, why can't I INSERT direct into the table to be returned, as in #sqlserver ?

However I did learn a few useful things that are worth sharing I think:

#ibmi Hints and Tips #18: create/replace table in QTEMP from SQL:
DECLARE GLOBAL TEMPORARY TABLE [table] ([col] [type], ... ) WITH REPLACE
(You can then use the table absolutely normally as QTEMP.[table].)

#ibmi Hints and Tips #19: FOR var1 as cur1 CURSOR FOR [SELECT statement] DO
[whatever, using column names direct from result set]; END FOR;
(I don't know whether you can do this in SQL Server, but I am definitely going to investigate.)

#ibmi Hints and Tips #20: table function usage: esp. note the final identifier (x here):
SELECT * FROM TABLE(MyTableFunction(parm1, ...)) x
(Don't like this super complicated syntax, either!)

Tuesday 25 July 2017

Making what you have work better: IBM DB2 for i SQL Performance Monitoring, Analysis and Tuning Workshop, IBM South Bank, 14-16 November 2017: not to be missed!

Mike Cain, of the IBM Labs, Rochester, Minnesota, a long term good friend of i-Community, will be coming to IBM South Bank on the 14th, 15th and 16th November to run his fabulous “IBM DB2 for i SQL Performance Monitoring, Analysis and Tuning Workshop”.

This workshop will ensure that those staff using SQL will understand the impact of doing it right and wrong and understand the best practice methodology for using SQL and the supporting tools to write efficient and performant code.

I attended this course myself in 2009 and cannot praise it too highly.

The agenda includes:
·       Introduction to DB2 for i Query Optimizer and Database Engine
·       Database engine methods for data access and data processing
·       Indexing and Statistics strategies
·       SQL application design and programming best practices
·       Behaviour of static SQL and dynamic SQL
·       Best practices for popular SQL interfaces, such as CLI, ODBC and JDBC
·       Parallel database processing - DB2 Symmetric Multiprocessor (SMP)
·       State of the art tools, including System i Navigator Performance Centre

If you are interested, drop me an email at mandy.shaw@iperimeter.co.uk and I will put you in touch with the course organisers.

Sunday 23 April 2017

Still here and still busy!

Apologies for yet another big gap in posting. Again both work and domestic matters have got in the way, but I am looking forward to a less manic period when some of the blog posts I have in my head might make it here.

Sorry not to have followed up on the 'auction' post promised last time but I decided it might not be a public domain matter.