Monthly Archives: December 2011

Mainframes

About hundred years ago, I worked on a mainframe. I those days, the mainframe was basically the mainstream computer that many people used. In those days, financial institutions started automating their business processes.
In the nowadays world, these mainframes are still the workhorses of these financial institutions. But how did we access them?
Now, I am back in a finncial institute where getting access to such an environment is very handy as the real work is done there. The PC is only used for Email purposes and minor Word editing. Unix is only used in the marketing department but the real stuff is in a mainframe.

So I had to refresh my mainframe knowledge.

But how?

I first got a free access to a mainframe. I found a possibility with http://www.efglobe.com/cgi-bin/mainframe/mainuser .
TSOAccess.
Within two days a clear message was given – I asked access Friday night and got replay Sunday afternoon. Good work. The message gave a clear indication on how to access the mainframe.

But to access a mainframe, you need a terminal application. This can be downloaded from http://x3270.bgp.nu/download.html . This is an open source application that emulates a 3270 terminal.

I recovered the knowledge with an old book Doug Lowe, “MVS TSO”. These three items (a book, a client terminal app and a TSO account) will help to renew my mainframe knowledge. Back in time.

Accessing Oracle from Perl

This weekend had bad weather. Rain pouring from the sky and a drop of snow.

Such weather is good to undertake something that is absolutely useless like writing a Perl programme on Linux to access a remote Oracle database.

The first hurdle to overcome is to install an Oracle client along with some Perl routines that will allow us to write Perl code to get things done. This hurdle can be overcome with help of http://usestrict.net/2009/07/12/perl-installing-dbdoracle-on-ubuntu-904-and-oracle-instant-client/ . This site gives a nice cookbook to install both the Oracle client and the Perl modules.

It took me about 4 hours to get this done. But then I had the software that I needed. The actual code that I wrote is:


#!/usr/bin/perl -wT

use Oraperl;

print "Content-type: text/html\r\n\r\n";
print "<!DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN>";
print "<html><head>";
print "<title>Test Perl Script</title>";
print "</head>";
$ENV{"ORACLE_HOME"} = "/usr/lib/oracle/xe/app/oracle/product/10.2.0/server";
$hConnection = &ora_login("xe", "uid", "psw") || die $ora_errstr;
$hQuery = &ora_open($hConnection, "SELECT naam, nummer from scott.klant") || die $ora_errstr;
@asQueryReturn = &ora_fetch($hQuery);
while (@asQueryReturn != 0)
{
print "<p>naam: $asQueryReturn[0] nummer: $asQueryReturn[1]";
@asQueryReturn = &ora_fetch($hQuery);
}
&ora_close($hQuery);
&ora_logoff($hConnection);
exit;

 

This code is not very well documented on the Internet. The best resource is http://www.tutorialspoint.com/perl/oraperl_manual.htm . Nevertheless I could not get this code right until I saw in another source (http://www.cs.purdue.edu/homes/cs290w/perlLecs/PerlOracle.html ) that you also give the full path to ORACLE_HOME as an environmental variable. This is a bit strange as this variable is also known on Linux, but without it, the code did not run.

I wrote this piece of code as a cgi programme. To retrieve the results, I had to start the output with:  print “Content-type: text/html\r\n\r\n”; and print “<!DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN>”; as to avoid the strings to get lost in the Internet. Only after these strings, the output is interpreted as HTML and it can be displayed in the browser.

 

 

Layers in Visio

Sorry to say, but I love Microsoft Visio. Plse do not spread the word. But from time to time, I had to create a document that served two distinct groups: a group who was just interested in getting a highlight and a group who wanted to go into details. In earlier times, I created two documents: one for the highlights and one for the details. But I then had to maintain two documents. A change in the highlights has to be introduced in the details as well.
This can be shown in next figure:

Visio2layers

The highlights are included in the first layer only; the details are composed of the first and second layer. If you maintain this in two documents, you will have to introduce a change in the first layer in two documents simultaneously.

However, this is not necessary as Viso knows the concepts of layers. One could add some objects to the first layer and some to the second. Subsequently, one may choose to print either the first or the second layers or both. This can be directly started when several layers are created.
Take view>Layer Properties

Visio2layers

Once the layers are created, one could add the objects to the layer. Take format>Layer:

Visio2layers

This could make life easier.
I owe thanks here to gert-Jan Kooren who pointed me at the Vision feature: it allows us to create complicate diagrams, that can also be shown at a lower level of detail. Nice!

Add blobs in your database

For some reason, I always overlooked the possibility to include pictures in a database. I decided to overcome this flaw and started experimenting with it.
The first step was to create a table in Access. In Access, we have the possibility to use the datatype “OLE Object”. A field with this datatype can be used to store pictures. The inclusion of a picture is then straightforward. Rightclick on the field brings you in a menu that allows you to include a picture:
screenprint
That was really easy.
Let us move to Oracle.
In Oracle, it is really easy sailing. Let us first create a table.

CREATE TABLE "SCOTT"."BLOBJE" 
   (	"NUMMER" NUMBER, 
	"PLAATJE" BFILE
   )

Then, we create a storage where to put the images in.

CREATE OR REPLACE DIRECTORY 
PLAATJE_DIR AS 
'/home/tom/oracle';

This is then followed by storing a nice picture in the storagearea (=/home/tom/oracle). Let us say, we stored a file “thumbsup.gif” there.
The final step is to store a record in the table that was created first:

INSERT INTO blobje VALUES(4,bfilename('PLAATJE_DIR','thumbsup.gif'));

We can then see the picture from within an Oracle table:
Oracle
Then with Teradata, it was not so simple. Like in Oracle, I created a table first:

CREATE SET TABLE financial.blobje
     (
      plaatje BLOB(2097088000),
      nummer INTEGER NOT NULL)
UNIQUE PRIMARY INDEX ( nummer );

I then changed the mode of Teradata SQL Assistant by listening to import. This was accomplished by File>Import Data. I then started a query, saying:

insert into  financial.blobje(nummer,plaatje) values (3,?B);

.
This started a dialogue where a reference was made to a control file that contained the name of the picture. The controlfile was stored in the same directory as the picture and it only contained the name of the picture.
This is not trivial. Nor could it be found in Teradata manual (at least I couldn’t). The information was retrieved from “http://developer.teradata.com/applications/articles/large-objects-part-1-loading”.

In this website, I found another solution that uses the BTEQWIN client. Let us first create a table:

create table financial.blobje(
    id varchar (80),
    binary_lob binary large object
)
unique primary index (id);

Then store several pictures in a directory (say in D:\tmaanen\Desktop\). Say we store “thumpsup.gif” and “thumbsdown.gif” in tht directory.
Also create an import.ctl file that contains:

D:\tmaanen\Desktop\thumbsup.gif,xxxx
D:\tmaanen\Desktop\thumbsdown.gif,yyyy

In BTEQWIN, we create a loop that reads the lines in this import.ctl file. This can be achieved by:

.import vartext ‘,’ LOBCOLS=1 file=’D:\tmaanen\Desktop\import.ctl’;

followed by:

.repeat *
using (lobFile blob as deferred,id varchar(40))
insert into financial.blobje (id, binary_lob)
values (:id, :lobFile);

It is not really easy, but it can be admittingly done.

Uoef:

teradata