25 Mar 12 Representation of numerics on a mainframe

I now work with a mainframe. I realised once more that numeric variables have different formats on a mainframe. As we do not always have a clear description, we should somehow recognise how the data are stored. Let us first show how numeric data can be stored.
As an example, I take the number 123.

In the upper row, we see the number 123. This is how we see externally this number. In the two rows below, we see the internal format: F1F2F3. This is the most common format: PIC(n) display in Cobol terminology. When we use SAS, we must use the s370fzdu6.2 format, the so-called unsigned zoned decimal.

This brought me the idea to show 6 different internal formats of 123, along with the Cobol format and the SAS format.

Let me give you the results:

How does 123 look           Likely Cobol representation	                        SAS format
internally?     externally
F0F0F0F1F2F3	000123      PIC 9(n) DISPLAY	                                s370fzdu6.2
C0F0F0F1F2F3	{00123      PIC S9(n) DISPLAY SIGN LEADING	                S370FZDL6.2
4EF0F0F1F2F3	+00123      PIC S9(n) DISPLAY SIGN LEADING SEPARATE	        S370FZDS6.2
F0F0F1F2F34E	00123+      PIC S9(n) DISPLAY SIGN TRAILING SEPARATE	        S370FZDT6.2
00000000123C	            pic S(9)  comp-3	                                s370fpd6.2
00000000123F	            PIC 9(n) PACKED-DECIMAL	                        s370fpdu6.2

In fact, two different formats are used:

• Zoned decimals where digits can be internally seen as a combination of F and a digit. Hence 7 is stored as F7. Externally, we see the 7. The only difference between the different zoned decimal representation is to whether a sign is available(the +) and where it is placed: at a trailing position or at a leading position.
• Packed decimals where digit are stored efficiently as one byte is used to internally store two digits. The result is we can recognise 123 in the internal format quite easily (in 00000000123C). However the external representation doesn’t make much sense.

17 Dec 11 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:

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

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

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!

07 Dec 11 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:

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:

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:

04 Sep 11 Installing Oracle – the host is localhost!

Yesterday, I started installing Oracle. I decided to download the newest version: version 11g R. I used windows xp as a platform with DHCP switched off. It really looked simple: the screens look simple and the questions asked were straight forward. I let it go for about 2 hours and after this time I returned to the machine and I looked if everything was successfull. It certainly was: an encooraging “successfull” looked at me.
I checked the installation by a command “tnsping”. This command was issued at the same machine where Oracle was installed.
I then issued the command “sqlplus uid/psw” that also gave access to the Oracle database.
Finally, I installed TOAD on the same machine as Oracle installed and also TOAD gave access to the database.
Much to my surprise, I could not get access from another machine as the machine on which I had installed Oracle. I got an error saying “TNS-12541: TNS: geen listener”. I really did not understand as to why this happened. If you google this error, you get ideas on network problems, but this was not the case. I checked every possible typo but this did not happen either.
The clue as to why the error happened came as I compared the outcomes form the tnsping command. From the server, it looked like:

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (20 msec)

look at the host! It reads localhost where I expected to see the serveraddress.

The outcome from the remote computer was:

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.9)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
TNS-12541: TNS: geen listener

the host is 192.168.1.9 which in itsself is ok.

Apparently the listener on the server listens to the localhost whereas it had to listen to the server address 192.168.1.9.

The solution was twofold.
First I changed the hosts file on the server in \windows\system32\drivers\etc. I commented out the localhost and I introduced the serveraddress.
Secondly, I changed the tnsnames.ora, listener.ora that can be found in \network\Administrator on the server. Every “localhost” reference was replaced by the serveraddress 192.168.1.9.

This seemed to help: the listener started to listen to the network address and it was possible to access the database from outside.

Oops – took a few hours but Oracle was up and running.

19 Jun 11 Ranking the rows

I was asked a few days to write a SQL that would retrieve the one but latest row. Take as an example a few rows below: we have a several functions with their min salary. The question is: which function earns the one but highest minumum salary. In the rows below, we have minimum salary 20000, 15000, 3000 and 8200. The outcome would be the row where the minimum salary 15000 is found as we should disregard the highest number (here 20000).

To this problem, we have two solutions:
One solution is:

SELECT rownum, min_salary from
(select min_salary from jobs order by min_salary desc)A ;

Here, we order the table first, then return the rows with the rownumber. This could then be filtered on rownumber=2.
The second solution is:

SELECT min_salary,
       RANK() OVER (ORDER BY min_salary desc) "rank"
FROM   jobs;

This makes use of the so-called analytical functions. It leads to the same result: after a filter on RANK()=2, we have the desired row.