Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276

    Unanswered: DB2 for iSeries error SQL0901 : CPF4204

    Hi, this query run ok:

    with gopa1 as
    (select a11.TKZTKT TKZTKT,
    a12.AAKCOO DLCO,
    a12.AADCT1 DLDCTO,
    a12.AADOC1 DLDOCO,
    sum(a11.TKZKNN) WJXBFS1
    from py812dta.F59R200 a11
    join db2dtamart.APLICACIONES a12
    on (a11.TKDCTO = a12.AADCT and
    a11.TKKCOO = a12.AAKCOO and
    a11.TKZTKT = a12.AAZTKT)
    join py812dta.F59LQ020 a13
    on (a12.AADCT1 = a13.DLDCTO and
    a12.AADOC1 = a13.DLDOCO and
    a12.AAKCOO = a13.DLCO)
    where (a13.DLZOFN in (501, 507, 511, 512, 522)
    and a12.AADCT1 in ('FB', 'BC')
    and a11.TKHARPER in ('1011', '1112')
    and a11.TKITM in (79804, 79855, 80055)
    and (exists (select *
    from db2dtamart.APLICACIONES c21
    where c21.AAZTKT = a11.TKZTKT
    and c21.AAKCOO = a12.AAKCOO
    and c21.AADCT1 = a12.AADCT1
    and c21.AADOC1 = a12.AADOC1)))
    group by a11.TKZTKT,
    a12.AAKCOO,
    a12.AADCT1,
    a12.AADOC1
    ),
    gopa2 as
    (select a11.AAZTKT TKZTKT,
    a11.AAKCOO DLCO,
    a11.AADCT1 DLDCTO,
    a11.AADOC1 DLDOCO,
    sum(a11.AAZKAP) WJXBFS1
    from db2dtamart.APLICACIONES a11
    join py812dta.F59R200 a12
    on (a11.AADCT = a12.TKDCTO and
    a11.AAKCOO = a12.TKKCOO and
    a11.AAZTKT = a12.TKZTKT)
    join py812dta.F59LQ020 a13
    on (a11.AADCT1 = a13.DLDCTO and
    a11.AADOC1 = a13.DLDOCO and
    a11.AAKCOO = a13.DLCO)
    where (a13.DLZOFN in (501, 507, 511, 512, 522)
    and a11.AADCT1 in ('FB', 'BC')
    and a12.TKHARPER in ('1011', '1112')
    and a12.TKITM in (79804, 79855, 80055))
    group by a11.AAZTKT,
    a11.AAKCOO,
    a11.AADCT1,
    a11.AADOC1
    ),
    gopa3 as
    (select a12.AAZTKT TKZTKT,
    a11.DLCO DLCO,
    a11.DLDCTO DLDCTO,
    a11.DLDOCO DLDOCO,
    sum(a11.DLZKNN) WJXBFS1
    from py812dta.F59LQ020 a11
    join db2dtamart.APLICACIONES a12
    on (a11.DLCO = a12.AAKCOO and
    a11.DLDCTO = a12.AADCT1 and
    a11.DLDOCO = a12.AADOC1)
    where (a11.DLZOFN in (501, 507, 511, 512, 522)
    and a11.DLDCTO in ('FB', 'BC')
    and a11.DLHARPER in ('1011', '1112')
    and a11.DLITM in (79804, 79855, 80055)
    and (exists (select *
    from db2dtamart.APLICACIONES c21
    where c21.AAZTKT = a12.AAZTKT
    and c21.AAKCOO = a11.DLCO
    and c21.AADCT1 = a11.DLDCTO
    and c21.AADOC1 = a11.DLDOCO)))
    group by a12.AAZTKT,
    a11.DLCO,
    a11.DLDCTO,
    a11.DLDOCO
    )
    ,
    gopa4 as
    (select pa11.TKZTKT TKZTKT,
    pa11.DLCO DLCO,
    pa11.DLDCTO DLDCTO,
    pa11.DLDOCO DLDOCO
    from gopa1 pa11
    union
    select pa11.TKZTKT TKZTKT,
    pa11.DLCO DLCO,
    pa11.DLDCTO DLDCTO,
    pa11.DLDOCO DLDOCO
    from gopa2 pa11
    union
    select pa11.TKZTKT TKZTKT,
    pa11.DLCO DLCO,
    pa11.DLDCTO DLDCTO,
    pa11.DLDOCO DLDOCO
    from gopa3 pa11 )

    select count(1)
    from gopa4 pa11
    left outer join gopa1 pa12
    on (pa11.DLCO = pa12.DLCO and
    pa11.DLDCTO = pa12.DLDCTO and
    pa11.DLDOCO = pa12.DLDOCO and
    pa11.TKZTKT = pa12.TKZTKT)
    left outer join gopa2 pa13
    on (pa11.DLCO = pa13.DLCO and
    pa11.DLDCTO = pa13.DLDCTO and
    pa11.DLDOCO = pa13.DLDOCO and
    pa11.TKZTKT = pa13.TKZTKT)
    left outer join gopa3 pa14
    on (pa11.DLCO = pa14.DLCO and
    pa11.DLDCTO = pa14.DLDCTO and
    pa11.DLDOCO = pa14.DLDOCO and
    pa11.TKZTKT = pa14.TKZTKT)
    join py812dta.F59LQ020 a15
    on (pa11.DLCO = a15.DLCO and
    pa11.DLDCTO = a15.DLDCTO and
    pa11.DLDOCO = a15.DLDOCO)
    join py812dta.F59C010 a16
    on (a15.DLCO = a16.COKCOO and
    a15.DLDCT1 = a16.CODCTO and
    a15.DLDOC1 = a16.CODOCO)
    but, when add a new join, for example:


    with gopa1 as
    (select a11.TKZTKT TKZTKT,
    a12.AAKCOO DLCO,
    a12.AADCT1 DLDCTO,
    a12.AADOC1 DLDOCO,
    sum(a11.TKZKNN) WJXBFS1
    from py812dta.F59R200 a11
    join db2dtamart.APLICACIONES a12
    on (a11.TKDCTO = a12.AADCT and
    a11.TKKCOO = a12.AAKCOO and
    a11.TKZTKT = a12.AAZTKT)
    join py812dta.F59LQ020 a13
    on (a12.AADCT1 = a13.DLDCTO and
    a12.AADOC1 = a13.DLDOCO and
    a12.AAKCOO = a13.DLCO)
    where (a13.DLZOFN in (501, 507, 511, 512, 522)
    and a12.AADCT1 in ('FB', 'BC')
    and a11.TKHARPER in ('1011', '1112')
    and a11.TKITM in (79804, 79855, 80055)
    and (exists (select *
    from db2dtamart.APLICACIONES c21
    where c21.AAZTKT = a11.TKZTKT
    and c21.AAKCOO = a12.AAKCOO
    and c21.AADCT1 = a12.AADCT1
    and c21.AADOC1 = a12.AADOC1)))
    group by a11.TKZTKT,
    a12.AAKCOO,
    a12.AADCT1,
    a12.AADOC1
    ),
    gopa2 as
    (select a11.AAZTKT TKZTKT,
    a11.AAKCOO DLCO,
    a11.AADCT1 DLDCTO,
    a11.AADOC1 DLDOCO,
    sum(a11.AAZKAP) WJXBFS1
    from db2dtamart.APLICACIONES a11
    join py812dta.F59R200 a12
    on (a11.AADCT = a12.TKDCTO and
    a11.AAKCOO = a12.TKKCOO and
    a11.AAZTKT = a12.TKZTKT)
    join py812dta.F59LQ020 a13
    on (a11.AADCT1 = a13.DLDCTO and
    a11.AADOC1 = a13.DLDOCO and
    a11.AAKCOO = a13.DLCO)
    where (a13.DLZOFN in (501, 507, 511, 512, 522)
    and a11.AADCT1 in ('FB', 'BC')
    and a12.TKHARPER in ('1011', '1112')
    and a12.TKITM in (79804, 79855, 80055))
    group by a11.AAZTKT,
    a11.AAKCOO,
    a11.AADCT1,
    a11.AADOC1
    ),
    gopa3 as
    (select a12.AAZTKT TKZTKT,
    a11.DLCO DLCO,
    a11.DLDCTO DLDCTO,
    a11.DLDOCO DLDOCO,
    sum(a11.DLZKNN) WJXBFS1
    from py812dta.F59LQ020 a11
    join db2dtamart.APLICACIONES a12
    on (a11.DLCO = a12.AAKCOO and
    a11.DLDCTO = a12.AADCT1 and
    a11.DLDOCO = a12.AADOC1)
    where (a11.DLZOFN in (501, 507, 511, 512, 522)
    and a11.DLDCTO in ('FB', 'BC')
    and a11.DLHARPER in ('1011', '1112')
    and a11.DLITM in (79804, 79855, 80055)
    and (exists (select *
    from db2dtamart.APLICACIONES c21
    where c21.AAZTKT = a12.AAZTKT
    and c21.AAKCOO = a11.DLCO
    and c21.AADCT1 = a11.DLDCTO
    and c21.AADOC1 = a11.DLDOCO)))
    group by a12.AAZTKT,
    a11.DLCO,
    a11.DLDCTO,
    a11.DLDOCO
    )
    ,
    gopa4 as
    (select pa11.TKZTKT TKZTKT,
    pa11.DLCO DLCO,
    pa11.DLDCTO DLDCTO,
    pa11.DLDOCO DLDOCO
    from gopa1 pa11
    union
    select pa11.TKZTKT TKZTKT,
    pa11.DLCO DLCO,
    pa11.DLDCTO DLDCTO,
    pa11.DLDOCO DLDOCO
    from gopa2 pa11
    union
    select pa11.TKZTKT TKZTKT,
    pa11.DLCO DLCO,
    pa11.DLDCTO DLDCTO,
    pa11.DLDOCO DLDOCO
    from gopa3 pa11 )

    select count(1)
    from gopa4 pa11
    left outer join gopa1 pa12
    on (pa11.DLCO = pa12.DLCO and
    pa11.DLDCTO = pa12.DLDCTO and
    pa11.DLDOCO = pa12.DLDOCO and
    pa11.TKZTKT = pa12.TKZTKT)
    left outer join gopa2 pa13
    on (pa11.DLCO = pa13.DLCO and
    pa11.DLDCTO = pa13.DLDCTO and
    pa11.DLDOCO = pa13.DLDOCO and
    pa11.TKZTKT = pa13.TKZTKT)
    left outer join gopa3 pa14
    on (pa11.DLCO = pa14.DLCO and
    pa11.DLDCTO = pa14.DLDCTO and
    pa11.DLDOCO = pa14.DLDOCO and
    pa11.TKZTKT = pa14.TKZTKT)
    join py812dta.F59LQ020 a15
    on (pa11.DLCO = a15.DLCO and
    pa11.DLDCTO = a15.DLDCTO and
    pa11.DLDOCO = a15.DLDOCO)
    join py812dta.F59C010 a16
    on (a15.DLCO = a16.COKCOO and
    a15.DLDCT1 = a16.CODCTO and
    a15.DLDOC1 = a16.CODOCO)

    join py812dta.f0101 a17
    on (a15.DLZOFN = a17.ABAN8)
    i get this error:

    Estado de SQL: 58004
    Código de proveedor: -901
    Mensaje: [SQL0901] Error del sistema SQL. Causa . . . . . : Se ha producido un error del sistema SQL. La sentencia SQL actual no puede finalizarse satisfactoriamente. El error no impedirá que se procesen otras sentencias SQL. Los mensajes anteriores pueden indicar que hay un problema con la sentencia SQL y SQL no determinó correctamente el error. El identificador del mensaje anterior era CPF4204. Se ha producido el tipo de error interno 3107. Si está precompilando, el proceso no continuará más allá de esta sentencia. Recuperación . : Consulte los mensajes anteriores para determinar si hay algún problema con la sentencia SQL. Para examinar los mensajes, utilice el mandato DSPJOBLOG si ejecuta de forma interactiva, o el mandato WRKJOB para examinar la salida de una precompilación. Un programa de aplicación que reciba este código de retorno puede intentar sentencias SQL posteriores. Corrija los errores y vuelva a intentar la petición.

    any idea?
    ( db2 for iseries v5r4 )
    TIA

  2. #2
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276

    Ignore_derived_index

    Anyone have experience with this parameter?

    IBM Receives system sql error when joining 2 tables. - United States

    in my case this parameter is DEFAULT

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by achiola View Post
    Anyone have experience with this parameter?

    IBM Receives system sql error when joining 2 tables. - United States

    in my case this parameter is DEFAULT
    The technote pointed by the URL looks like for Software version: 7.1.

    But, if you doubt the relationship with your issue and the technote,
    you can try by yourself.
    Resolving the problem
    Based on the advise of IBM the value for the column 'IGNORE_DERIVED_INDEX ' of the file QSYS/QAQQINI was changed from *DEFAULT to *YES. This change solved the issue.
    The value can be changed by means of interactive sql on the AS400 (STRSQL)

  4. #4
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    tonkuma, thanks for your replay. Yes I read this note, but, I dont know the impact of this parameter, do you changed this parameter? do you have experiencie with the impact of this change?
    thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •