Reading db2 clob from binary download

Question

More then a year ago I asked about reading DB2 EBCDIC data in a .Net program. The amazingly helpful answer included a routine for reading VARCHAR columns.

I am now faced with reading CLOB columns.

Is their internal structure similar in that the first word contains the actual size? Or is it the whole word, considering the possible length of the CLOB is much larger?

Thanks!

Mononucleosis

It seems that while for a VarCahr the first 2 bytes hold actual size, for a CLOB, it the first 4 bytes. Will be glad to hear confirmation or otherwise... – Mononucleosis

Answer 1

Not the mainframe but DB2/400 (midrange) appears to store CLOB fields externally from the table itself:

CREATE TABLE QTEMP/T1 (F1 CLOB(512))
INSERT INTO QTEMP/T1 (F1) VALUES ('TEST')

SELECT * FROM QTEMP/T1

F1
*POINTER

DSPPFM QTEMP/T1

* . . .  + . . .  . 1 . .  . . + .  . . . 2  . . . .  + . . .  . 3 . .
00000000 00000000 00000000 00000000 5CD7D6C9 D5E3C5D9 40404040 40404040

*...+....1....+....2....+....3..
*POINTER

Is there any particular reason you're not accessing the database using a DB/2 driver to handle all of these details? It's like reading from SQL server by trying to parse the .MDF files.

---

See IBM Redbooks | Large Objects with DB2 for z/OS and OS/390 chapter 4.4.1 LOB table space organization for more information.

Fenestra

The reason is that getting the permissions to do a direct link to a bank's mainframe is not something I want my sales process to depend on... – Mononucleosis

Answer 2

This is a much more detail explanation of how to do so http://ripalsoni.wordpress.com/2008/07/24/how-do-i-read-write-oracle-clob-data-in-aspnet-or-vbnet/

Step 1: Add a reference – Oracle.Dataaccess.dll ( found in ODP.NET )

Step 2: Imports following namespaces

    Imports Oracle.DataAccess.Client
    Imports Oracle.DataAccess.Types

Step 3: Create a connection string

    Public ReadOnly connectionstring = "data source = oradb;user id = rmsoni;password=rmsoni99"

Step 4: Create Following Public Methods

Public Sub ReadLOBData()
Dim con As New OracleConnection(connectionstring)
con.Open()
Dim sql As String = "select CLOBTEXTFIELD from TestCLOB where ID=1"
Dim cmd As OracleCommand = New OracleCommand(sql, con)
Dim dr As OracleDataReader = cmd.ExecuteReader()
dr.Read()
Dim blob As OracleClob = dr.GetOracleClob(0)
txtOutput.Text = blob.Value()
blob.Close()
dr.Close()
con.Close()
End Sub

Complete Source Code –

Public Sub WriteLOBData()
Dim connection As New OracleConnection(connectionstring)
connection.Open()

Dim strSQL As String = "INSERT INTO TestCLOB (ID,CLOBTEXTFIELD) VALUES (1,:TEXT_DATA) "
Dim paramData As New OracleParameter
paramData.Direction = ParameterDirection.Input
paramData.OracleDbType = OracleDbType.Clob
paramData.ParameterName = "TEXT_DATA"
paramData.Value = txtInput.Text

Dim cmd As New OracleCommand
cmd.Connection = connection
cmd.Parameters.Add(paramData)
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()

paramData = Nothing
cmd = Nothing
connection.Close()
End Sub

Vittle

Thenks for the detailed answer, but as the tag clearly says, I need to do this for DB2 data. – Mononucleosis