DB2 multi-row insert problem

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Multi-row insert in DB2 luw works with this statement 
   INSERT INTO TABLE1 VALUES ('A','B','C'), ('D','E','F')

2. Multi-row insert in mainframe DB2 Z/OS doesen't work with the same 
statement, because it needs : 
   INSERT INTO TABLE1 VALUES (:HV1, :HV2,:HV3) FOR :N ROWS    

The question is: 

Why ibm_db creates the same statement (the first one) in both cases
when I execute SYNCDB ? 

Thanks 
a.Peta   

Original issue reported on code.google.com by gasp...@gmail.com on 4 Jul 2012 at 2:12

[GoogleCodeExporter]

"INSERT INTO TABLE1 VALUES ('A','B','C'), ('D','E','F')" type of sql not 
supported in ZOS, this one is realy a bug for our django adapter. 

"INSERT INTO TABLE1 VALUES (:HV1, :HV2,:HV3) FOR :N ROWS" type of sql for 
multi-row insert only work for embedded SQL.

If you want then we will provide you a patch with some workaround, or we will 
fixed it in our next release.

Thanks,
Rahul Priyadarshi

Original comment by rahul.pr...@in.ibm.com on 9 Jul 2012 at 8:50

[GoogleCodeExporter]

hi Raul
Thank you if you can send it as soon as possible.

My email:
a.peta @ icsc.it

greetings A.peta

Original comment by gasp...@gmail.com on 10 Jul 2012 at 8:54

[GoogleCodeExporter]

Could you please update us about your client environment like Python version, 
operating system etc.

Thanks,
Rahul

Original comment by rahul.pr...@in.ibm.com on 10 Jul 2012 at 9:32

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Hi Raul,

this is our environment:
 - O.S.: Windows server 2008 sp2 32 bit
 - Python 2.7.3
 - Django 1.4
 - Client DB2 9.5.0 

Thanks
A.Peta

Original comment by gasp...@gmail.com on 10 Jul 2012 at 9:58

[GoogleCodeExporter]

Hi A.Peta,
 There was some problem with the our ZOS systems, and I wants to check the whole test suit first before giving you the patch but with no luck. Still they are not up.

Please disable the has_bulk_insert flag in base.py file.

base.py @ line #70
 supports_regex_backreferencing = False
    supports_timezones = False
    has_bulk_insert = True
    has_select_for_update = True

change it to "has_bulk_insert = False"

In case of any problem please let me know.

Thanks,
Rahul 

Original comment by rahul.pr...@in.ibm.com on 12 Jul 2012 at 12:19

[GoogleCodeExporter]

Hi Rahul,

we manually patch the base.py module with:
    has_bulk_insert = False

and we show that the insert sql statement changes correctly to:

    INSERT INTO "AUTH_PERMISSION" ("NAME", "CONTENT_TYPE_ID", "CODENAME") VALUES (?, ?, ?)

but an error occurs in the post-sync handlers,

executing the syncdb, with -v 2, the error stack is:

$ ***_manage.py syncdb -v 2
Creating tables ...
Running post-sync handlers for application auth
*** SELECT "DJANGO_CONTENT_TYPE"."ID", "DJANGO_CONTENT_TYPE"."NAME", 
"DJANGO_CONTENT_TYPE"."APP_LABEL", "DJANGO_CONTENT_TYPE"."MODEL" FROM 
"DJANGO_CONTENT_TYPE" WHERE ( "DJANGO_CONTENT_TYPE"."MODEL" = ?  AND  
"DJANGO_CONTENT_TYPE"."APP_LABEL" = ? )
*** SELECT "DJANGO_CONTENT_TYPE"."ID", "DJANGO_CONTENT_TYPE"."NAME", 
"DJANGO_CONTENT_TYPE"."APP_LABEL", "DJANGO_CONTENT_TYPE"."MODEL" FROM 
"DJANGO_CONTENT_TYPE" WHERE ( "DJANGO_CONTENT_TYPE"."MODEL" = ?  AND  
"DJANGO_CONTENT_TYPE"."APP_LABEL" = ? )
*** SELECT "DJANGO_CONTENT_TYPE"."ID", "DJANGO_CONTENT_TYPE"."NAME", 
"DJANGO_CONTENT_TYPE"."APP_LABEL", "DJANGO_CONTENT_TYPE"."MODEL" FROM 
"DJANGO_CONTENT_TYPE" WHERE ( "DJANGO_CONTENT_TYPE"."MODEL" = ?  AND  
"DJANGO_CONTENT_TYPE"."APP_LABEL" = ? )
*** SELECT "AUTH_PERMISSION"."CONTENT_TYPE_ID", "AUTH_PERMISSION"."CODENAME" 
FROM "AUTH_PERMISSION" INNER JOIN "DJANGO_CONTENT_TYPE" ON 
("AUTH_PERMISSION"."CONTENT_TYPE_ID" = "DJANGO_CONTENT_TYPE"."ID") WHERE  
"AUTH_PERMISSION"."CONTENT_TYPE_ID" IN (?, ?, ?) ORDER BY 
"DJANGO_CONTENT_TYPE"."APP_LABEL" ASC, "DJANGO_CONTENT_TYPE"."MODEL" ASC, 
"AUTH_PERMISSION"."CODENAME" ASC
*** INSERT INTO "AUTH_PERMISSION" ("NAME", "CONTENT_TYPE_ID", "CODENAME") 
VALUES (?, ?, ?)
*** (u'Can add permission', 1, u'add_permission')
*** [IBM][CLI Driver][DB2] SQL0803N  One or more values in the INSERT 
statement, UPDATE statement, or foreign key update caused by a DELETE statement 
are not valid because the primary key, unique constraint or unique index 
identified by "AUTH1XER" constrains table "000000020C" from having duplicate 
values for the index key.  SQLSTATE=23505 SQLCODE=-803
Traceback (most recent call last):
  File "***/***_manage.py", line 15, in <module>
    management.execute_from_command_line()
  File "***/lib/python2.7/site-packages/django/core/management/__init__.py", line 443, in execute_from_command_line
    utility.execute()
  File "***/lib/python2.7/site-packages/django/core/management/__init__.py", line 382, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "***/lib/python2.7/site-packages/django/core/management/base.py", line 196, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "***/lib/python2.7/site-packages/django/core/management/base.py", line 232, in execute
    output = self.handle(*args, **options)
  File "***/lib/python2.7/site-packages/django/core/management/base.py", line 371, in handle
    return self.handle_noargs(**options)
  File "***/lib/python2.7/site-packages/django/core/management/commands/syncdb.py", line 110, in handle_noargs
    emit_post_sync_signal(created_models, verbosity, interactive, db)
  File "***/lib/python2.7/site-packages/django/core/management/sql.py", line 189, in emit_post_sync_signal
    interactive=interactive, db=db)
  File "***/lib/python2.7/site-packages/django/dispatch/dispatcher.py", line 172, in send
    response = receiver(signal=self, sender=sender, **named)
  File "***/lib/python2.7/site-packages/django/contrib/auth/management/__init__.py", line 54, in create_permissions
    auth_app.Permission.objects.bulk_create(objs)
  File "***/lib/python2.7/site-packages/django/db/models/manager.py", line 140, in bulk_create
    return self.get_query_set().bulk_create(*args, **kwargs)
  File "***/lib/python2.7/site-packages/django/db/models/query.py", line 416, in bulk_create
    self.model._base_manager._insert(objs_without_pk, fields=[f for f in fields if not isinstance(f, AutoField)], using=self.db)
  File "***/lib/python2.7/site-packages/django/db/models/manager.py", line 203, in _insert
    return insert_query(self.model, objs, fields, **kwargs)
  File "***/lib/python2.7/site-packages/django/db/models/query.py", line 1576, in insert_query
    return query.get_compiler(using=using).execute_sql(return_id)
  File "***/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 910, in execute_sql
    cursor.execute(sql, params)
  File "***/lib/python2.7/site-packages/django/db/backends/util.py", line 40, in execute
    return self.cursor.execute(sql, params)
  File "***/lib/python2.7/site-packages/ibm_db_django/pybase.py", line 135, in execute
    return super( DB2CursorWrapper, self ).execute( operation, parameters )
  File "***/lib/python2.7/site-packages/ibm_db_dbi.py", line 1300, in execute
    self._execute_helper(parameters)
  File "***/lib/python2.7/site-packages/ibm_db_dbi.py", line 1208, in _execute_helper
    raise self.messages[len(self.messages) - 1]
django.db.utils.IntegrityError

Any suggestion is greatly appreciated.

Thanks 
    A.Peta 

Original comment by gasp...@gmail.com on 16 Jul 2012 at 11:27

[GoogleCodeExporter]

I tried to reproduce the above issue but with no luck. I tried with a default 
created project without altering anything.

The above error you are getting since in "DJANGO_CONTENT_TYPE" table, the row 
corresponding to the CONTENT_TYPE_ID of AUTH_PERMISSION table doesn't exist, so 
the Foreign Key relationship breaks here. 

Please try with an empty database and if the problem still exist then please 
update us the modification you have made in default project.

Thanks,
Rahul

Original comment by rahul.pr...@in.ibm.com on 17 Jul 2012 at 12:48

[GoogleCodeExporter]

Hi Rahul,

we try the run syncdb on a empty database, 

but we take the error:

[IBM][CLI Driver][DB2] SQL0199N  The use of the reserved word "FOR" following 
"" is not valid.  Expected tokens may include:  "= .".  SQLSTATE=42601 
SQLCODE=-199

for to the statement:

SET INTEGRITY FOR "EXTRANET_IMPIEGATOICSC" OFF CASCADE DEFERRED;

if we try to run syncdb again (with a non empty database) we get the error:

[IBM][CLI Driver][DB2] SQL0803N  One or more values in the INSERT statement, 
UPDATE statement, or foreign key update caused by a DELETE statement are not 
valid because the primary key, unique constraint or unique index identified by 
"AUTH1Z3X" constrains table "0000000201" from having duplicate values for the 
index key.  SQLSTATE=23505 SQLCODE=-803

for to the statement:

INSERT INTO "AUTH_PERMISSION" ("NAME", "CONTENT_TYPE_ID", "CODENAME") VALUES 
(?, ?, ?)

Thank 
A.Peta

Original comment by gasp...@gmail.com on 18 Jul 2012 at 12:59

[GoogleCodeExporter]

could you use the newer version of ibm_db_django adaptor, and let me know if 
you still face the problem.

Thanks,
rahul

Original comment by rahul.pr...@in.ibm.com on 17 Jun 2013 at 6:28