Ever had a user asking for a report you couldn't generate again because it was a "snapshot" of your database at a certain time? Here's how to fix that!

This TechTip will tell you all you need to know about the BLOB. No, we're not talking about the 1958 Steve McQueen movie or the Marvel villain. We're going to revisit a fairly old concept, with a new twist: the Binary Large OBject. I'll explain how and why you can use it in the context of iSeries-generated PDFs and charts. Read on to find out more.

A BLOB is, basically, a way to store a non-native object on a DB2 database. By non-native object, I mean a file, such as a PDF, a spreadsheet, or a web page. This can be especially useful when your application generates reports in these (or other) formats and you need to retrieve them quickly, without having to produce the report all over again. In fact, if your report is a "snapshot" of a certain moment (like the end of the month or quarter, for instance), it might be hard to produce the exact same report.

As you might have guessed by now, the DB2 implementation of the BLOB concept consists of a kind of column on a database table. I say "kind of" because you cannot actually query this column; all you'll see is a pointer reference. In order to access the content, you need to extract it to the IFS.

Let's start by creating a table with some information to help us identify what's inside each BLOB:

CREATE TABLE RPTARCHIVE/REPORTS

(RPT_ID DECIMAL (7 , 0)

, RPT_TYPE CHAR (10 )

, RPT_TIME TIMESTAMP

, RPT_RMK CHAR (200 )

, RPT_FILE1 BLOB (500K )

, CONSTRAINT PK_RPT_ID UNIQUE (RPT_ID))

Rpt_Id is the unique identifier of the stored file, and Rpt_Type and Rpt_Rmk (short for remark) will help you identify the content without having to extract it to the IFS. Finally, I've created the BLOB itself (Rpt_File1) with 500K. This is something you need to handle with care, because the BLOB size is not dynamic or dependant on the content size. In other words, each line of this table will occupy a little of 500K of disk space, regardless of the file size contained within the BLOB. Another detail that might be important for the development management of this table is that it cannot be created with a DDS. Currently, there's no way to create a BLOB-type column using DDS keywords.

Now that we have our "report archive," it's time to store something on it. In order to do that, you need to use a special data structure composed of the following fields:

• File Name Length (10u 0)—The size of the complete file and path name in characters
• Data Length (10u 0)—The size of the file (an optional parameter)
• File Option (10u 0)—The operation you want to execute (I'll explain this further on)
• File Name (255A )—The complete path and file name

The Blob_In sample program stores the PDF file /Reports/Test.PDF into the REPORTS table:

     D Rpt_ID         S             7 0 Inz(*Zeros)

     D Rpt_Type       S             10   Inz(*Blanks)

     D Rpt_Rmk         S           255   Inz(*Blanks)

     D File_In         s                   SQLTYPE(BLOB_FILE)

     * The SQLTYPE(BLOB_FILE) definition will be converted by the compiler

     * into the following data structure:

     D*File_In         DS

     D*File_In_NL                   10U 0

     D*File_In_DL                   10U 0

     D*File_In_FO                   10U 0

     D*File_In_NAME                 255A

     // Store an object into the blob table

     /FREE

       Rpt_ID = 1;

       Rpt_Type = 'PDF';

       Rpt_Rmk = 'Just a PDF test report';

       File_In_FO   = SQFRD;

       File_In_NAME = '/Reports/Test.PDF';

       File_In_NL   = %len(%trimr(File_In_NAME));

       EXEC SQL   Insert Into RptArchive/Reports

                         Values (:Rpt_ID, :Rpt_Type, NOW(),

                                :Rpt_Rmk , :File_In);

       *InLr = *On;

     /END-FREE

Here, I'm passing all the relevant information into the special data structure and executing a simple INSERT statement. Note that I didn't actually define the File_In data structure; I defined a SQLTYPE(BLOB_File) field, which the compiler converts into the special data structure I mentioned above.

If you query the REPORTS table, you'll see the newly inserted record. As I mentioned before, you can't actually see the file inside the BLOB; you will see a pointer instead. That's why I added the Rpt_Type and Rpt_Remark columns to the table.

Now, let's retrieve the file from the archive, with the BLOB_OUT sample program: 

     D Rpt_ID         S             7 0 Inz(*Zeros)

     D File_Out       s                   SQLTYPE(BLOB_FILE)

     * The SQLTYPE(BLOB_FILE) definition will be converted by the compiler

     * into the following data structure:

     D*File_Out       DS

     D*File_Out_NL                   10U 0

     D*File_Out_DL                   10U 0

     D*File_Out_FO                   10U 0

     D*File_Out_NAME               255A

     // Retrieve an object from the blob table

     /FREE

       Rpt_ID = 1;

       File_Out_FO   = SQFOVR;

       File_Out_NAME = '/Reports/Test_Out.PDF';

       File_Out_NL   = %Len(%TrimR(File_Out_NAME));

       EXEC SQL   Select Rpt_File1

                   Into :File_Out

                   From RptArchive/Reports

                   Where Rpt_Id = :Rpt_Id;

       *InLr = *On;

     /END-FREE

Again, I'm passing all the relevant information to the special data structure in order to retrieve the file with a SELECT statement. Other than the SQL statement used, the big difference between the two programs is the file option I used (…_FO field of the special data structure). In the first program, it was SQFRD, which tells the program I'm using the file in "read mode"; in the second program, I wanted to write the file to the IFS, overwriting it if it existed. For that I chose SQFOVR. Here's the complete list of file options available:

• SQFRD—Open IFS file for reading only
• SQFCRT—Create IFS file if it doesn't exist, and open for writing
• SQFOVR—Create IFS file if it doesn't exist, overwrite it if it does exist, and open for writing
• SQFAPP—Append data to the end of an existing IFS file

In the second part of this TechTip, I'll walk you through the creation of simplified store/retrieve procedures that can be used in both RPG and SQL!