Topic
  • 5 replies
  • Latest Post - ‏2005-11-28T22:35:42Z by ldubois
SystemAdmin
SystemAdmin
3129 Posts

Pinned topic IBM DB2 UDB for iSeries OLE DB Provider questions

‏2004-08-13T16:25:37Z |
This is a bit long, but please bear with me. I am trying to include enough
details so that someone can help without this becoming a super long thread
with many messages back and forth to get all the info necessary.

I have setup my iSeries as a linked server in SQL Server using the IBM DB2
UDB for iSeries OLE DB Provider. I cannot seem to run AS400 stored
procedures. I am also having problems running INSERT, UPDATE, and DELETE
commands against AS400 files using this linked server.

If I try to issue the INSERT, UPDATE and DELETE commands using a four part
name to identify the AS400 files, I receive the following error message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'IBMDA400' reported an error. [OLE/DB provider returned
message: CPF4328: Member EMP not journaled to journal *N.]

I am also having a probem executing an AS400 stored procedure. I am fairly
certain that the problem is not with the procedure because I can run it
successfully from iSeries Navigator. When I try to run a stored procedure
using EXEC LINKSERVER.DBNAME.LIBRARY.PROC I receive the following message:

Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'PROC' on remote server 'LINKSERVER'. [OLE/DB
provider returned message: SQL0104: Token { was not valid. Valid
tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
Cause . . . . . : A syntax error was detected at token {. Token { is not
a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the statement
is correct up to the token. The error may be earlier in the statement, but
the syntax of the statement appears to be valid up to this point. Recovery .
. . : Do one or more of the following and try the request again: --
Verify the SQL statement in the area of the token {. Correct the statement.
The error could be a missing comma or quotation mark, it could be a
misspelled word, or it could be related to the order of clauses. -- If the
error token is <END-OF-STATEMENT>, correct the SQL statement because it does
not end with a valid clause.]

If I try to run the stored procedure as a CALL in an OPENQUERY like this
SELECT * FROM OPENQUERY(LINKSERVER, 'CALL LIBRARY.PROC'), I receive the
following error message:

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CALL LIBRARY.PROC'. The OLE DB provider 'IBMDA400'
indicates that the object has no columns.

I am using SQL Server 2000 under Windows 2000 Server Edition. I have
installed iSeries Access for Windows V5R2M0 Service Level SI14299 and my
iSeries is at V5R2M0. I have installed MDAC 2.8.

Does anyone have any ideas how I should change my setup or the way that I
issue the commands? All help will be greatly appreciated.
Evan B. Nelson, III
Princess House, Inc.
ebnelson@nospamprincesshouse.com

Take out the nospam to email.
Updated on 2005-11-28T22:35:42Z at 2005-11-28T22:35:42Z by ldubois
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: IBM DB2 UDB for iSeries OLE DB Provider questions

    ‏2004-08-27T14:47:42Z  
    Does your call return anything?

    I realize this KB article is not an exact match of your problem but it's similarity could provide some clues.

    (Microsoft Knowledge Base Article - 270119 )
    http://support.microsoft.com/default.aspx?scid=kb;en-us;270119
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: IBM DB2 UDB for iSeries OLE DB Provider questions

    ‏2004-09-12T04:21:38Z  
    This comes from a db2 troubleshooting guide:
    CPF4328 Member nnnnnnnnnn not journaled to journal *N
    where nnnnnnnnnn is the name of the file/table.
    Action
    To resolve this problem, ensure that the target table is being journaled.

    To require Journaling is the default, but it can be overridden. Care should
    be used if you don't use journaling if several users are accessing the table
    concurrently because you could get updates to the table out of order.

    To Journal a file, the following is required:

    Create a Journal Receiver -- CRTJRNRCV

    Create a Journal -- CRTJRN (use the name of the journal receiver created
    above)

    Start journaling on the file -- STRJRNPF EMP

    Be sure to "commit" afterwards or all of your transactions will rollback.
    "Evan Nelson" <ebnelsonREMOVECAPSANDINVALID@princesshouse.com> wrote in
    message news:cfipjf$5b70$1@news.boulder.ibm.com...
    > This is a bit long, but please bear with me. I am trying to include
    > enough
    > details so that someone can help without this becoming a super long thread
    > with many messages back and forth to get all the info necessary.
    >
    > I have setup my iSeries as a linked server in SQL Server using the IBM DB2
    > UDB for iSeries OLE DB Provider. I cannot seem to run AS400 stored
    > procedures. I am also having problems running INSERT, UPDATE, and DELETE
    > commands against AS400 files using this linked server.
    >
    > If I try to issue the INSERT, UPDATE and DELETE commands using a four part
    > name to identify the AS400 files, I receive the following error message:
    >
    > Server: Msg 7399, Level 16, State 1, Line 1
    > OLE DB provider 'IBMDA400' reported an error. [OLE/DB provider returned
    > message: CPF4328: Member EMP not journaled to journal *N.]
    >
    > I am also having a probem executing an AS400 stored procedure. I am
    > fairly
    > certain that the problem is not with the procedure because I can run it
    > successfully from iSeries Navigator. When I try to run a stored procedure
    > using EXEC LINKSERVER.DBNAME.LIBRARY.PROC I receive the following message:
    >
    > Server: Msg 7212, Level 17, State 1, Line 1
    > Could not execute procedure 'PROC' on remote server 'LINKSERVER'. [OLE/DB
    > provider returned message: SQL0104: Token { was not valid. Valid
    > tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
    > Cause . . . . . : A syntax error was detected at token {. Token { is
    > not
    > a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
    > HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the
    > statement
    > is correct up to the token. The error may be earlier in the statement,
    > but
    > the syntax of the statement appears to be valid up to this point. Recovery
    > .
    > . . : Do one or more of the following and try the request again: --
    > Verify the SQL statement in the area of the token {. Correct the
    > statement.
    > The error could be a missing comma or quotation mark, it could be a
    > misspelled word, or it could be related to the order of clauses. -- If the
    > error token is <END-OF-STATEMENT>, correct the SQL statement because it
    > does
    > not end with a valid clause.]
    >
    > If I try to run the stored procedure as a CALL in an OPENQUERY like this
    > SELECT * FROM OPENQUERY(LINKSERVER, 'CALL LIBRARY.PROC'), I receive the
    > following error message:
    >
    > Server: Msg 7357, Level 16, State 2, Line 1
    > Could not process object 'CALL LIBRARY.PROC'. The OLE DB provider
    > 'IBMDA400'
    > indicates that the object has no columns.
    >
    > I am using SQL Server 2000 under Windows 2000 Server Edition. I have
    > installed iSeries Access for Windows V5R2M0 Service Level SI14299 and my
    > iSeries is at V5R2M0. I have installed MDAC 2.8.
    >
    > Does anyone have any ideas how I should change my setup or the way that I
    > issue the commands? All help will be greatly appreciated.
    >
    >
    > Evan B. Nelson, III
    > Princess House, Inc.
    > ebnelson@nospamprincesshouse.com
    >
    > Take out the nospam to email.
    >
    >

  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: IBM DB2 UDB for iSeries OLE DB Provider questions

    ‏2005-10-30T22:14:35Z  
    This comes from a db2 troubleshooting guide:
    CPF4328 Member nnnnnnnnnn not journaled to journal *N
    where nnnnnnnnnn is the name of the file/table.
    Action
    To resolve this problem, ensure that the target table is being journaled.

    To require Journaling is the default, but it can be overridden. Care should
    be used if you don't use journaling if several users are accessing the table
    concurrently because you could get updates to the table out of order.

    To Journal a file, the following is required:

    Create a Journal Receiver -- CRTJRNRCV

    Create a Journal -- CRTJRN (use the name of the journal receiver created
    above)

    Start journaling on the file -- STRJRNPF EMP

    Be sure to "commit" afterwards or all of your transactions will rollback.
    "Evan Nelson" <ebnelsonREMOVECAPSANDINVALID@princesshouse.com> wrote in
    message news:cfipjf$5b70$1@news.boulder.ibm.com...
    > This is a bit long, but please bear with me. I am trying to include
    > enough
    > details so that someone can help without this becoming a super long thread
    > with many messages back and forth to get all the info necessary.
    >
    > I have setup my iSeries as a linked server in SQL Server using the IBM DB2
    > UDB for iSeries OLE DB Provider. I cannot seem to run AS400 stored
    > procedures. I am also having problems running INSERT, UPDATE, and DELETE
    > commands against AS400 files using this linked server.
    >
    > If I try to issue the INSERT, UPDATE and DELETE commands using a four part
    > name to identify the AS400 files, I receive the following error message:
    >
    > Server: Msg 7399, Level 16, State 1, Line 1
    > OLE DB provider 'IBMDA400' reported an error. [OLE/DB provider returned
    > message: CPF4328: Member EMP not journaled to journal *N.]
    >
    > I am also having a probem executing an AS400 stored procedure. I am
    > fairly
    > certain that the problem is not with the procedure because I can run it
    > successfully from iSeries Navigator. When I try to run a stored procedure
    > using EXEC LINKSERVER.DBNAME.LIBRARY.PROC I receive the following message:
    >
    > Server: Msg 7212, Level 17, State 1, Line 1
    > Could not execute procedure 'PROC' on remote server 'LINKSERVER'. [OLE/DB
    > provider returned message: SQL0104: Token { was not valid. Valid
    > tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
    > Cause . . . . . : A syntax error was detected at token {. Token { is
    > not
    > a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
    > HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the
    > statement
    > is correct up to the token. The error may be earlier in the statement,
    > but
    > the syntax of the statement appears to be valid up to this point. Recovery
    > .
    > . . : Do one or more of the following and try the request again: --
    > Verify the SQL statement in the area of the token {. Correct the
    > statement.
    > The error could be a missing comma or quotation mark, it could be a
    > misspelled word, or it could be related to the order of clauses. -- If the
    > error token is <END-OF-STATEMENT>, correct the SQL statement because it
    > does
    > not end with a valid clause.]
    >
    > If I try to run the stored procedure as a CALL in an OPENQUERY like this
    > SELECT * FROM OPENQUERY(LINKSERVER, 'CALL LIBRARY.PROC'), I receive the
    > following error message:
    >
    > Server: Msg 7357, Level 16, State 2, Line 1
    > Could not process object 'CALL LIBRARY.PROC'. The OLE DB provider
    > 'IBMDA400'
    > indicates that the object has no columns.
    >
    > I am using SQL Server 2000 under Windows 2000 Server Edition. I have
    > installed iSeries Access for Windows V5R2M0 Service Level SI14299 and my
    > iSeries is at V5R2M0. I have installed MDAC 2.8.
    >
    > Does anyone have any ideas how I should change my setup or the way that I
    > issue the commands? All help will be greatly appreciated.
    >
    >
    > Evan B. Nelson, III
    > Princess House, Inc.
    > ebnelson@nospamprincesshouse.com
    >
    > Take out the nospam to email.
    >
    >

    Dear all,

    I am having the same error while trying to invoke a iSeries DB2 stored procedure from VB.Net. Actually the error occured while trying to execute the code such commandName.executeNonQuery. My SP is just a select statement. Following is the VB code:

    Dim connection As New Odbc.OdbcConnection
    Dim exchCmd As Odbc.OdbcCommand
    Dim parmCCY As Odbc.OdbcParameter
    Dim parmTOR As Odbc.OdbcParameter
    Dim parmBUY As Odbc.OdbcParameter
    Dim parmSELL As Odbc.OdbcParameter
    Dim sRate As DataSet

    Try
    connection = Me.OdbcConnection1
    exchCmd = New Odbc.OdbcCommand("EXCHANGE(CSD12690)")
    exchCmd.Connection = connection
    exchCmd.CommandType = CommandType.StoredProcedure
    parmCCY = exchCmd.Parameters.Add("PCCY", Odbc.OdbcType.Char, 3)
    parmCCY.Direction = ParameterDirection.InputOutput
    parmCCY.Value = Currency
    parmTOR = exchCmd.Parameters.Add("PTOR", Odbc.OdbcType.Decimal)
    parmTOR.Direction = ParameterDirection.Output
    parmBUY = exchCmd.Parameters.Add("PBUY", Odbc.OdbcType.Decimal)
    parmBUY.Direction = ParameterDirection.Output
    parmSELL = exchCmd.Parameters.Add("PSELL", Odbc.OdbcType.Decimal)
    parmSELL.Direction = ParameterDirection.Output
    connection.Open()

    'THE ERROR OCCURED HERE....
    exchCmd.ExecuteNonQuery()

    sRate= parmCCY.Value & " " & parmTOR.Value & " " & parmBUY.Value & " " & parmSELL.Value
    connection.Close()
    connection.Dispose()
    Catch ex As Exception
    sRate = ex.Message
    End Try
    Return sRate
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: IBM DB2 UDB for iSeries OLE DB Provider questions

    ‏2005-11-10T21:56:01Z  
    Dear all,

    I am having the same error while trying to invoke a iSeries DB2 stored procedure from VB.Net. Actually the error occured while trying to execute the code such commandName.executeNonQuery. My SP is just a select statement. Following is the VB code:

    Dim connection As New Odbc.OdbcConnection
    Dim exchCmd As Odbc.OdbcCommand
    Dim parmCCY As Odbc.OdbcParameter
    Dim parmTOR As Odbc.OdbcParameter
    Dim parmBUY As Odbc.OdbcParameter
    Dim parmSELL As Odbc.OdbcParameter
    Dim sRate As DataSet

    Try
    connection = Me.OdbcConnection1
    exchCmd = New Odbc.OdbcCommand("EXCHANGE(CSD12690)")
    exchCmd.Connection = connection
    exchCmd.CommandType = CommandType.StoredProcedure
    parmCCY = exchCmd.Parameters.Add("PCCY", Odbc.OdbcType.Char, 3)
    parmCCY.Direction = ParameterDirection.InputOutput
    parmCCY.Value = Currency
    parmTOR = exchCmd.Parameters.Add("PTOR", Odbc.OdbcType.Decimal)
    parmTOR.Direction = ParameterDirection.Output
    parmBUY = exchCmd.Parameters.Add("PBUY", Odbc.OdbcType.Decimal)
    parmBUY.Direction = ParameterDirection.Output
    parmSELL = exchCmd.Parameters.Add("PSELL", Odbc.OdbcType.Decimal)
    parmSELL.Direction = ParameterDirection.Output
    connection.Open()

    'THE ERROR OCCURED HERE....
    exchCmd.ExecuteNonQuery()

    sRate= parmCCY.Value & " " & parmTOR.Value & " " & parmBUY.Value & " " & parmSELL.Value
    connection.Close()
    connection.Dispose()
    Catch ex As Exception
    sRate = ex.Message
    End Try
    Return sRate
    Have you attempted to use the dotnet data provider from IBM. It should have come with your IBM iSeries Access for Windows and do a Selective Setup.
  • ldubois
    ldubois
    122 Posts

    Re: IBM DB2 UDB for iSeries OLE DB Provider questions

    ‏2005-11-28T22:35:42Z  
    This comes from a db2 troubleshooting guide:
    CPF4328 Member nnnnnnnnnn not journaled to journal *N
    where nnnnnnnnnn is the name of the file/table.
    Action
    To resolve this problem, ensure that the target table is being journaled.

    To require Journaling is the default, but it can be overridden. Care should
    be used if you don't use journaling if several users are accessing the table
    concurrently because you could get updates to the table out of order.

    To Journal a file, the following is required:

    Create a Journal Receiver -- CRTJRNRCV

    Create a Journal -- CRTJRN (use the name of the journal receiver created
    above)

    Start journaling on the file -- STRJRNPF EMP

    Be sure to "commit" afterwards or all of your transactions will rollback.
    "Evan Nelson" <ebnelsonREMOVECAPSANDINVALID@princesshouse.com> wrote in
    message news:cfipjf$5b70$1@news.boulder.ibm.com...
    > This is a bit long, but please bear with me. I am trying to include
    > enough
    > details so that someone can help without this becoming a super long thread
    > with many messages back and forth to get all the info necessary.
    >
    > I have setup my iSeries as a linked server in SQL Server using the IBM DB2
    > UDB for iSeries OLE DB Provider. I cannot seem to run AS400 stored
    > procedures. I am also having problems running INSERT, UPDATE, and DELETE
    > commands against AS400 files using this linked server.
    >
    > If I try to issue the INSERT, UPDATE and DELETE commands using a four part
    > name to identify the AS400 files, I receive the following error message:
    >
    > Server: Msg 7399, Level 16, State 1, Line 1
    > OLE DB provider 'IBMDA400' reported an error. [OLE/DB provider returned
    > message: CPF4328: Member EMP not journaled to journal *N.]
    >
    > I am also having a probem executing an AS400 stored procedure. I am
    > fairly
    > certain that the problem is not with the procedure because I can run it
    > successfully from iSeries Navigator. When I try to run a stored procedure
    > using EXEC LINKSERVER.DBNAME.LIBRARY.PROC I receive the following message:
    >
    > Server: Msg 7212, Level 17, State 1, Line 1
    > Could not execute procedure 'PROC' on remote server 'LINKSERVER'. [OLE/DB
    > provider returned message: SQL0104: Token { was not valid. Valid
    > tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
    > Cause . . . . . : A syntax error was detected at token {. Token { is
    > not
    > a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
    > HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the
    > statement
    > is correct up to the token. The error may be earlier in the statement,
    > but
    > the syntax of the statement appears to be valid up to this point. Recovery
    > .
    > . . : Do one or more of the following and try the request again: --
    > Verify the SQL statement in the area of the token {. Correct the
    > statement.
    > The error could be a missing comma or quotation mark, it could be a
    > misspelled word, or it could be related to the order of clauses. -- If the
    > error token is <END-OF-STATEMENT>, correct the SQL statement because it
    > does
    > not end with a valid clause.]
    >
    > If I try to run the stored procedure as a CALL in an OPENQUERY like this
    > SELECT * FROM OPENQUERY(LINKSERVER, 'CALL LIBRARY.PROC'), I receive the
    > following error message:
    >
    > Server: Msg 7357, Level 16, State 2, Line 1
    > Could not process object 'CALL LIBRARY.PROC'. The OLE DB provider
    > 'IBMDA400'
    > indicates that the object has no columns.
    >
    > I am using SQL Server 2000 under Windows 2000 Server Edition. I have
    > installed iSeries Access for Windows V5R2M0 Service Level SI14299 and my
    > iSeries is at V5R2M0. I have installed MDAC 2.8.
    >
    > Does anyone have any ideas how I should change my setup or the way that I
    > issue the commands? All help will be greatly appreciated.
    >
    >
    > Evan B. Nelson, III
    > Princess House, Inc.
    > ebnelson@nospamprincesshouse.com
    >
    > Take out the nospam to email.
    >
    >

    If you don't want to use journaling, you can use the IBMDASQL provider instead of IBMDA400. IBMDASQL is an SQL-only provider shipped with iSeries Access for Windows starting in v5r3m0.