sql1229n the current transaction has been rolled back

**dnboa** · 01/06/2012, 16h12

Hello,

I have an error message when I want to create a table on my db2 database.

The current transaction has been rolled back because of a system error.. SQLCODE=-1229, SQLSTATE=40504, DRIVER=4.7.89

I'm using DB2 v9.7.

Can someone help me?

Best regards,
Dnboa

**asmduty** · 13/06/2012, 10h22

Hello,

here is the help from IBM for your error :

http://publib.boulder.ibm.com/infoce...sql01229n.html

It seems to be due to a connection error.

**Oratorio** · 04/07/2012, 14h26

cela arrive quand t'as une opération qui empêche de locker des tables système. . comme la construction d'une MQT avec load par exemple

regarde les locks avec snapshot_lock

sur unix j'ai développé ce ksh :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
#!/bin/ksh
 
###########################################################
# affiche les locks                                       #
#                                                         #
# INPUT:  DBALIAS                                         #
# OUTPUT: terminal                                        #
#                                                         #
# creation 26-10-2011.                                          #
###########################################################
 
L_DB=$1
if [ "${L_DB}" = "" ]; then
   echo "specify db parameter"
   exit 4
fi
 
# see /usr/opt/db2_09_07/include/sqlmon.h for meanings
 
clear
db2 +o connect to ${L_DB}
db2 "select distinct cast (a.agent_id as char(8)) "APPLI_ID", 
 
-- ******************************************************************************
-- application status (appl_status)
-- ******************************************************************************
-- define SQLM_INIT                 0            /* Performing initialization  */
-- define SQLM_CONNECTPEND          1            /* connect pending             */
-- define SQLM_CONNECTED            2            /* connect completed           */
-- define SQLM_UOWEXEC              3            /* UOW executing               */
-- define SQLM_UOWWAIT              4            /* UOW waiting                 */
-- define SQLM_LOCKWAIT             5            /* lock wait                   */
-- define SQLM_COMMIT_ACT           6            /* commit active               */
-- define SQLM_ROLLBACK_ACT         7            /* rollback active             */
-- define SQLM_RECOMP               8            /* recompiling a plan          */
-- define SQLM_COMP                 9            /* compiling a SQL statement   */
-- define SQLM_INTR                10            /* request interrupted         */
-- define SQLM_DISCONNECTPEND      11            /* disconnect pending          */
-- define SQLM_TPREP               12            /* Prepared transaction        */
-- define SQLM_THCOMT              13            /* heuristically committed     */
-- define SQLM_THABRT              14            /* heuristically rolled back   */
-- define SQLM_TEND                15            /* Transaction ended           */
-- define SQLM_CREATE_DB           16            /* Creating Database           */
-- define SQLM_RESTART             17            /* Restarting a Database       */
-- define SQLM_RESTORE             18            /* Restoring a Database        */
-- define SQLM_BACKUP              19            /* Performing a Backup         */
-- define SQLM_LOAD                20            /* Performing a fast load      */
-- define SQLM_UNLOAD              21            /* Performing a fast unload    */
-- define SQLM_IOERROR_WAIT        22            /* Wait to disable tablespace  */
-- define SQLM_QUIESCE_TABLESPACE  23            /* Quiescing a tablespace      */
-- define SQLM_WAITFOR_REMOTE      24            /* Waiting for remote node     */
-- define SQLM_REMOTE_RQST         25            /* Pending results from remote request */
-- define SQLM_DECOUPLED           26            /* App has been decoupled from coord */
-- define SQLM_ROLLBACK_TO_SAVEPOINT 27          /* Rollback to savepoint       */
-- define SQLM_AUTONOMOUS_WAIT     28            /* Autonomous Routine Wait     */
-- define SQLM_UOWQUEUED           29            /* WLM activity is queued      */
 
            cast (case when appl_status = 0
    			      	        then 'Performing initialization' 
                       when appl_status = 1
    			      	        then 'connect pending' 
                       when appl_status = 2
    			      	        then 'connect completed' 
                       when appl_status = 3
    			      	        then 'UOW executing' 
                       when appl_status = 4
    			      	        then 'UOW waiting' 
                       when appl_status = 5
    			      	        then 'lock wait' 
                       when appl_status = 6
    			      	        then 'commit active' 
                       when appl_status = 7
    			      	        then 'rollback active' 
                       when appl_status = 8
    			      	        then 'recompiling a plan' 
                       when appl_status = 9
    			      	        then 'compiling a SQL statement' 
                       when appl_status = 10
    			      	        then 'request interrupted' 
                       when appl_status = 11
    			      	        then 'disconnect pending' 
                       when appl_status = 12
    			      	        then 'Prepared transaction' 
                       when appl_status = 13
    			      	        then 'heuristically committed' 
                       when appl_status = 14
    			      	        then 'heuristically rolled back' 
                       when appl_status = 15
    			      	        then 'Transaction ended' 
                       when appl_status = 16
    			      	        then 'Creating Database' 
                       when appl_status = 17
    			      	        then 'Restarting a Database' 
                       when appl_status = 18
    			      	        then 'Restoring a Database' 
                       when appl_status = 19
    			      	        then 'Performing a Backup' 
                       when appl_status = 20
    			      	        then 'Performing a fast load' 
                       when appl_status = 21
    			      	        then 'Performing a fast unload' 
                       when appl_status = 22
    			      	        then 'Wait to disable TS' 
                       when appl_status = 23
    			      	        then 'Quiescing a tablespace' 
                       when appl_status = 24
    			      	        then 'Waiting for remote node' 
                       when appl_status = 25
    			      	        then 'Pending results from RQ' 
                       when appl_status = 26
    			      	        then 'App decoupled from coord' 
                       when appl_status = 27
    			      	        then 'Rollback to savepoint' 
                       when appl_status = 28
    			      	        then 'Autonomous Routine Wait' 
                       when appl_status = 29
    			      	        then 'WLM activity is queued' 
    			             else 'Unknown'
    			        end as char(25)) as "APPL_STATUS",
 
--/******************************************************************************/
--/* Statement Operation Types (stmt_operation)                                 */
--/******************************************************************************/
--/* SQL operations */
--#define SQLM_PREPARE            1        /* SQL Prepare                       */
--#define SQLM_EXECUTE            2        /* SQL Execute                       */
--#define SQLM_EXECUTE_IMMEDIATE  3        /* SQL Execute Immediate             */
--#define SQLM_OPEN               4        /* SQL Open                          */
--#define SQLM_FETCH              5        /* SQL Fetch                         */
--#define SQLM_CLOSE              6        /* SQL Close                         */
--#define SQLM_DESCRIBE           7        /* SQL Describe                      */
--#define SQLM_STATIC_COMMIT      8        /* SQL Static Commit                 */
--#define SQLM_STATIC_ROLLBACK    9        /* SQL Static Rollback               */
--#define SQLM_FREE_LOCATOR      10        /* SQL Free Locator                  */
--#define SQLM_PREP_COMMIT       11        /* Prepare to commit (2-phase commit)*/
--#define SQLM_CALL              12        /* Call a stored procedure           */
--#define SQLM_SELECT            15        /* SELECT statement                  */
--#define SQLM_PREP_OPEN         16        /* Prep. and open (DB2 Connect only) */
--#define SQLM_PREP_EXEC         17        /* Prep. and execute (DB2 Connect)   */
--#define SQLM_COMPILE           18        /* Compile (DB2 Connect only)        */
--#define SQLM_SET               19        /* SET statement                     */
 
/* non-SQL operations */
--#define SQLM_RUNSTATS          20        /* Runstats                          */
--#define SQLM_REORG             21        /* Reorg                             */
--#define SQLM_REBIND            22        /* Rebind package                    */
--#define SQLM_REDIST            23        /* Redistribute                      */
--#define SQLM_GETTA             24        /* Get Table Authorization           */
--#define SQLM_GETAA             25        /* Get Administrative Authorization  */
--#define SQLM_GETNEXTCHUNK      26        /* DRDA Get Next Chunk command       */
--#define SQLM_DRPPKG            27        /* Drop Package                      */
 
            cast (case when c.STMT_OPERATION = 1
 			      	            then 'SQL Prepare'
    			             when c.STMT_OPERATION = 2
    			      	        then 'SQL Execute'
    			             when c.STMT_OPERATION = 3
    			      	        then 'SQL Exec Immediate'
    			             when c.STMT_OPERATION = 4
    			      	        then 'SQL Open'
    			             when c.STMT_OPERATION = 5
    			      	        then 'SQL Fetch'
    			             when c.STMT_OPERATION = 6
    			      	        then 'SQL Close'
    			             when c.STMT_OPERATION = 8
    			      	        then 'SQL Static Commit'
    			             when c.STMT_OPERATION = 9
    			      	        then 'SQL Static Rollback'
    			             when c.STMT_OPERATION = 12
    			      	        then 'Call a stored procedure'
    			             when c.STMT_OPERATION = 15
    			      	        then 'SELECT statement'
    			             when c.STMT_OPERATION = 20
    			      	        then 'Runstats'
    			             when c.STMT_OPERATION = 21
    			      	        then 'Reorg'
    			                else cast (c.STMT_OPERATION as char(20))
                  end as char(20)) as "LAST_OPERATION",
 
            cast (auth_id as char(10)) as "DB_USER",
            cast (execution_id as char(16)) as "OS_USER",
 
-- ******************************************************************************
-- * lock object type (lock_object_type)
-- ******************************************************************************
 
-- define SQLM_TABLE_LOCK            1    /* table lock type                   */
-- define SQLM_ROW_LOCK              2    /* table row lock type               */
-- define SQLM_INTERNAL_LOCK         3    /* Internal lock type                */
-- define SQLM_TABLESPACE_LOCK       4    /* Tablespace lock type              */
-- define SQLM_EOT_LOCK              5    /* end of table lock                 */
-- define SQLM_KEYVALUE_LOCK         6    /* key value lock                    */
-- define SQLM_SYSBOOT_LOCK          7    /* Internal lock on the sysboot table*/
-- define SQLM_INTERNALP_LOCK        8    /* Internal Plan lock                */
-- define SQLM_INTERNALV_LOCK        9    /* Internal Variation lock           */
-- define SQLM_INTERNALS_LOCK        10   /* Internal Sequence lock            */
-- define SQLM_INTERNALJ_LOCK        11   /* Bufferpool lock                   */
-- define SQLM_INTERNALL_LOCK        12   /* Internal Long/Lob lock            */
-- define SQLM_INTERNALC_LOCK        13   /* Internal Catalog Cache lock       */
-- define SQLM_INTERNALB_LOCK        14   /* Internal Online Backup lock       */
-- define SQLM_INTERNALO_LOCK        15   /* Internal Object Table lock        */
-- define SQLM_INTERNALT_LOCK        16   /* Internal Table Alter lock         */
-- define SQLM_INTERNALQ_LOCK        17   /* Internal DMS Sequence lock        */
-- define SQLM_INPLACE_REORG_LOCK    18   /* Inplace reorg lock                */
-- define SQLM_BLOCK_LOCK            19   /* Block lock type                   */
-- define SQLM_TABLE_PART_LOCK       20   /* Table partition lock type         */
-- define SQLM_AUTORESIZE_LOCK       21   /* Autoresize lock type              */
-- define SQLM_AUTOSTORAGE_LOCK      22   /* Automatic storage lock type       */
-- define SQLM_XML_PATH_LOCK         23   /* XML Path lock type                */
-- define SQLM_INTERNALM_LOCK        24   /* Internal Extent Movement lock     */
 
            cast (case when lock_object_type = 1
    			      	        then 'Table' 
    			             when lock_object_type = 2
    			      	        then 'Table row' 
    			             when lock_object_type = 3
    			      	        then 'Internal' 
    			             when lock_object_type = 4
    			      	        then 'Tablespace' 
    			             when lock_object_type = 5
    			      	        then 'end of table' 
    			             when lock_object_type = 6
    			      	        then 'key value' 
    			             when lock_object_type = 7
    			      	        then 'Internal on the sysboot' 
    			             when lock_object_type = 8
    			      	        then 'Internal Plan' 
    			             when lock_object_type = 9
    			      	        then 'Internal Variation' 
    			             when lock_object_type = 10
    			      	        then 'Internal Sequence' 
    			             when lock_object_type = 11
    			      	        then 'Bufferpool' 
    			             when lock_object_type = 12
    			      	        then 'Internal Long/Lob' 
    			             when lock_object_type = 13
    			      	        then 'Internal Catalog Cache' 
    			             when lock_object_type = 14
    			      	        then 'Internal Online Backup' 
    			             when lock_object_type = 15
    			      	        then 'Internal Object Table' 
    			             when lock_object_type = 16
    			      	        then 'Internal Table Alter' 
    			             when lock_object_type = 17
    			      	        then 'Internal DMS Sequence' 
    			             when lock_object_type = 18
    			      	        then 'Inplace reorg' 
    			             when lock_object_type = 19
    			      	        then 'Block' 
    			             when lock_object_type = 20
    			      	        then 'Table partition'
    			             when lock_object_type = 21
    			      	        then 'Autoresize'
    			             when lock_object_type = 22
    			      	        then 'Automatic storage'
    			             when lock_object_type = 23
    			      	        then 'XML Path'
    			             when lock_object_type = 24
    			      	        then 'Internal Extent Movement'
    			             else 'Unknown' 
    			        end as char(25)) as "OBJECT_TYPE",
 
-- ******************************************************************************
-- * lock modes (lock_mode)
-- ******************************************************************************
 
-- define SQLM_LNON  0                  /*  No Lock                             */
-- define SQLM_LOIS  1                  /*  Intention Share Lock                */
-- define SQLM_LOIX  2                  /*  Intention Exclusive Lock            */
-- define SQLM_LOOS  3                  /*  Share Lock                          */
-- define SQLM_LSIX  4                  /*  Share with Intention Exclusive Lock */
-- define SQLM_LOOX  5                  /*  Exclusive Lock                      */
-- define SQLM_LOIN  6                  /*  Intent None (For Dirty Read)        */
-- define SQLM_LOOZ  7                  /*  Super Exclusive Lock                */
-- define SQLM_LOOU  8                  /*  Update Lock                         */
-- define SQLM_LONS  9                  /*  Share Lock (CS/RS)                  */
-- define SQLM_LONX 10                  /*  Next-key Exclusive Lock             */
-- define SQLM_LOOW 11                  /*  Weak Exclusive Lock                 */
-- define SQLM_LONW 12                  /*  Next-key Weak Exclusive Lock        */
 
            cast (case when lock_mode = 0
 			      	            then 'No Lock'
    			             when lock_mode = 1
    			      	        then 'Intention Share'
    			             when lock_mode = 2
    			      	        then 'Intention Ex'
    			             when lock_mode = 3
    			      	        then 'Share'
    			             when lock_mode = 4
    			      	        then 'Share with Int. Ex'
    			             when lock_mode = 5
    			      	        then 'Exclusive'
    			             when lock_mode = 6
    			      	        then 'None: Dirty Read'
    			             when lock_mode = 7
    			      	        then 'Super Exclusive'
    			             when lock_mode = 8
    			      	        then 'Update'
    			             when lock_mode = 9
    			      	        then 'Share (CS/RS)'
    			             when lock_mode = 10
    			      	        then 'Next-key Exclusive'
    			             when lock_mode = 11
    			      	        then 'Weak Exclusive'
    			             when lock_mode = 12
    			      	        then 'Next-key Weak Ex'
    			             else 'Unknown'
    			        end as char(18)) as "MODE",
 
-- ******************************************************************************
-- * lock status (lock_status)
-- ******************************************************************************
 
-- define SQLM_LRBGRNT      1                     /*  Granted State             */
-- define SQLM_LRBCONV      2                     /*  Converting state          */
 
                  cast (case when lock_status = 1
    			      	        then 'G'
                             when lock_status = 2
    			      	        then 'C'
    			             else 'U'
                        end as char(1)) as "S",
            cast(trim(table_name) as char(25)) as TABLE,
            cast (tablespace_name as char(16)) as TABLESPACE
     from table(snapshot_lock('${L_DB}', 0)) a,
          table(snapshot_appl_info('${L_DB}',-1)) b,
          table(SNAPSHOT_STATEMENT('${L_DB}',-1)) c
     where a.agent_id = b.agent_id
       and a.agent_id = c.agent_id"
db2 +o terminate

sql1229n the current transaction has been rolled back

DB2

Discussions similaires

Partager

Partager