001 /** 002 * Copyright (c) 2003 Daffodil Software Ltd all rights reserved, 003 * Modifications Copyright (c) 2008 Regiscope Digital Imaging Co, LLC, All rights reserved. 004 * This program is free software; you can redistribute it and/or modify 005 * it under the terms of version 2 of the GNU General Public License as 006 * published by the Free Software Foundation. 007 * There are special exceptions to the terms and conditions of the GPL 008 * as it is applied to this software. See the GNU General Public License for more details. 009 * 010 * This program is distributed in the hope that it will be useful, 011 * but WITHOUT ANY WARRANTY; without even the implied warranty of 012 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 013 * GNU General Public License for more details. 014 * 015 * You should have received a copy of the GNU General Public License 016 * along with this program; if not, write to the Free Software 017 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA 018 */ 019 020 package org.dbreplicator.replication.DBHandler; 021 022 import java.sql.*; 023 import java.util.*; 024 025 import org.dbreplicator.replication.*; 026 import org.dbreplicator.replication.column.*; 027 import org.apache.log4j.Logger; 028 029 /** 030 * Method overrides specific to DB2. 031 */ 032 public class DB2Handler 033 extends AbstractDataBaseHandler { 034 protected static Logger log = Logger.getLogger(DB2Handler.class.getName()); 035 public DB2Handler() {} 036 037 public DB2Handler(ConnectionPool connectionPool0) { 038 connectionPool = connectionPool0; 039 vendorType = Utility.DataBase_DB2; 040 } 041 042 protected void createSuperLogTable(String pubName) throws SQLException, 043 RepException { 044 StringBuffer logTableQuery = new StringBuffer(); 045 logTableQuery.append(" Create Table ") 046 .append(log_Table) 047 .append(" ( " + RepConstants.logTable_commonId1 + 048 " bigint GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), " + 049 RepConstants.logTable_tableName2 + " varchar(255) ) "); 050 runDDL(pubName, logTableQuery.toString()); 051 StringBuffer indexQuery = new StringBuffer(); 052 indexQuery.append("CREATE INDEX ") 053 .append(RepConstants.log_Index) 054 .append(" ON " + getLogTableName()) 055 .append("(") 056 .append(RepConstants.logTable_commonId1) 057 .append(")"); 058 //System.out.println(" Create Index on LogTable : " + indexQuery.toString()); 059 runDDL(pubName, indexQuery.toString()); 060 061 } 062 063 /** 064 * Because changes has been made in structure of RepTable 065 * by Hisar team.So old method has been commented. After 066 * proper testing with all data base it should be deleted. 067 */ 068 069 /* protected void createRepTable(String pubName) throws SQLException, 070 RepException { 071 StringBuffer repTableQuery = new StringBuffer(); 072 repTableQuery.append(" Create Table ").append(getRepTableName()) 073 .append("( " + RepConstants.repTable_pubsubName1 + 074 " varchar(255) not null , " + RepConstants.repTable_tableId2 + 075 " bigint GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), ") 076 .append(" " + RepConstants.repTable_tableName2 + 077 " varchar(255) not null, " + 078 RepConstants.repTable_filter_clause3 + " varchar(255), ") 079 .append(" " + RepConstants.repTable_conflict_resolver4 + 080 " varchar(255) , Primary Key ( " + 081 RepConstants.repTable_pubsubName1 + " , " + 082 RepConstants.repTable_tableName2 + " ) ) "); 083 runDDL(pubName, repTableQuery.toString()); 084 } */ 085 086 protected void createRepTable(String pubName) throws SQLException, 087 RepException { 088 StringBuffer repTableQuery = new StringBuffer(); 089 repTableQuery.append(" Create Table ").append(getRepTableName()).append( 090 " ( ") 091 .append(RepConstants.repTable_pubsubName1).append( 092 " varchar(255) not null, ") 093 .append(RepConstants.repTable_tableId2).append( 094 " bigint GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), ") 095 .append(RepConstants.repTable_tableName2).append( 096 " varchar(255) not null , ") 097 .append(RepConstants.repTable_filter_clause3).append( 098 " varchar(255) , ") 099 .append(RepConstants.repTable_createshadowtable6).append( 100 " char(1) Default 'Y', ") 101 .append(RepConstants.repTable_cyclicdependency7).append( 102 " char(1) Default 'N', ") 103 .append(RepConstants.repTable_conflict_resolver4).append( 104 " varchar(255), ") 105 .append(" Primary Key (").append(RepConstants.repTable_pubsubName1). 106 append(" , ") 107 .append(RepConstants.repTable_tableName2).append(" ) ) "); 108 runDDL(pubName, repTableQuery.toString()); 109 } 110 111 protected void createPublicationTable(String pubName) throws RepException, 112 SQLException { 113 StringBuffer pubsTableQuery = new StringBuffer(); 114 pubsTableQuery.append(" Create Table ") 115 .append(getPublicationTableName()) 116 .append(" ( " + RepConstants.publication_pubName1 + 117 " varchar(255) not null Primary Key , " + 118 RepConstants.publication_conflictResolver2 + 119 " varchar(255) , ") 120 .append(" " + RepConstants.publication_serverName3 + 121 " varchar (255) ) "); 122 runDDL(pubName, pubsTableQuery.toString()); 123 } 124 125 protected void createBookMarkTable(String pubName) throws SQLException, 126 RepException { 127 StringBuffer bookmarkTableQuery = new StringBuffer(); 128 bookmarkTableQuery.append(" Create Table ") 129 .append("rep_bookmarktable") 130 .append(" ( " + RepConstants.bookmark_LocalName1 + 131 " varchar(255) not null , " + 132 RepConstants.bookmark_RemoteName2 + " varchar(255) not null, ") 133 .append(" " + RepConstants.bookmark_TableName3 + 134 " varchar(255) not null, " + RepConstants.bookmark_lastSyncId4 + 135 " bigint , ") 136 .append( 137 " " + RepConstants.bookmark_ConisderedId5 + " bigint ,") 138 .append(RepConstants.bookmark_IsDeletedTable) 139 .append(" char(1) not null default 'n', Primary Key ( " + 140 RepConstants.bookmark_LocalName1 + ", " + 141 RepConstants.bookmark_RemoteName2 + ", " + 142 RepConstants.bookmark_TableName3 + ") ) "); 143 runDDL(pubName, bookmarkTableQuery.toString()); 144 } 145 146 public void createShadowTable(String pubsubName, String tableName, 147 String allColseq,String[] primaryColumns) throws RepException { 148 StringBuffer shadowTableQuery = new StringBuffer(); 149 shadowTableQuery.append(" Create Table ") 150 .append(RepConstants.shadow_Table(tableName)) 151 .append(" ( " + RepConstants.shadow_sync_id1 + 152 " bigint GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) , ") 153 .append(" " + RepConstants.shadow_common_id2 + " BIGINT , ") 154 .append(" " + RepConstants.shadow_operation3 + " char(1) , ") 155 .append(" " + RepConstants.shadow_status4 + " char(1) ") 156 .append(allColseq) 157 .append(" , " + RepConstants.shadow_serverName_n + " varchar(255) ") 158 .append(" , " + RepConstants.shadow_PK_Changed + " char(1) ) "); 159 try { 160 //System.out.println("shadowTableQuery.toString() ="+shadowTableQuery.toString()); 161 runDDL(pubsubName, shadowTableQuery.toString()); 162 createIndex(pubsubName, RepConstants.shadow_Table(tableName)); 163 } 164 catch (RepException ex) { 165 throw ex; 166 } 167 catch (SQLException ex) { 168 // ex.printStackTrace(); 169 // Ignore the Exception 170 } 171 createIndex(pubsubName, RepConstants.shadow_Table(tableName)); 172 } 173 174 protected void createSubscriptionTable(String pubName) throws RepException, 175 SQLException { 176 String subsTableQuery = " Create Table " 177 + getSubscriptionTableName() 178 + " ( " + RepConstants.subscription_subName1 + 179 " varchar(255) NOT NULL Primary Key , " 180 + " " + RepConstants.subscription_pubName2 + " varchar(255) , " 181 + " " + RepConstants.subscription_conflictResolver3 + 182 " varchar(255) , " 183 + " " + RepConstants.subscription_serverName4 + " varchar (255)) "; 184 runDDL(pubName, subsTableQuery); 185 } 186 187 protected void createScheduleTable(String subName) throws SQLException, 188 RepException { 189 StringBuffer ScheduleTableQuery = new StringBuffer(); 190 191 ScheduleTableQuery.append(" Create Table ") 192 .append(getScheduleTableName()) 193 .append(" ( " + RepConstants.schedule_Name + " varchar(255) not null, " + 194 RepConstants.subscription_subName1 + 195 " varchar(255) not null unique , ") 196 .append(" " + RepConstants.schedule_type + " varchar(255) , ") 197 .append(" " + RepConstants.publication_serverName3 + " varchar (255) ," + 198 RepConstants.publication_portNo + " varchar(255) ,") 199 .append(" " + RepConstants.recurrence_type + 200 " varchar(255) , " + 201 RepConstants.replication_type + 202 " varchar(255) ,") 203 .append(" " + RepConstants.schedule_time + " bigint , ") 204 .append(" " + RepConstants.schedule_counter + 205 " bigint , Primary Key (" + 206 RepConstants.schedule_Name + 207 " , " + RepConstants.subscription_subName1 + 208 ") ) "); 209 runDDL(subName, ScheduleTableQuery.toString()); 210 // System.out.println(ScheduleTableQuery.toString()); 211 } 212 213 public void createShadowTableTriggers(String pubsubName, String tableName, 214 ArrayList colInfoList, 215 String[] primCols) throws RepException { 216 217 String serverName = getLocalServerName(); 218 // RepPrinter.print(" Columns are :::::: " + java.util.Arrays.asList(columnTypeInfoMap.keySet().toArray(new String[0]))); 219 // String[] colNames = (String[]) columnTypeInfoMap.keySet().toArray(new String[0]); 220 int size = colInfoList.size(); 221 String[] colNames = new String[size]; 222 for (int i = 0; i < size; i++) { 223 colNames[i] = ( (ColumnsInfo) colInfoList.get(i)).getColumnName(); 224 } 225 //RepPrinter.print(" Columns are :::::: " + java.util.Arrays.asList(colNames)); 226 String colNameSeq = getColumnNameSequence(colNames, "").toString(); 227 String colNameSeqPrefixOldRow = getColumnNameSequence(colNames, "oldRow."). 228 toString(); 229 String colNameSeqPrefixNewRow = getColumnNameSequence(colNames, "newRow."). 230 toString(); 231 String shadowTableName = RepConstants.shadow_Table(tableName); 232 String primColumnNamesSeq = getColumnNameSequence(primCols, "rep_old_"); 233 String primColNameSeqPrefixOldRow = getColumnNameSequence(primCols,"oldRow.").toString(); 234 String primColNameSeqPrefixNewRow = getColumnNameSequence(primCols,"newRow.").toString(); 235 String[] primOldCols =getColumnNameWithOldOrNewPrefix(primCols,"oldRow."); 236 String[] primNewCols =getColumnNameWithOldOrNewPrefix(primCols,"newRow."); 237 238 StringBuffer insertLogTable = new StringBuffer(); 239 insertLogTable.append(" Insert into ") 240 .append(log_Table) 241 .append(" ( ").append(RepConstants.logTable_tableName2) 242 .append(" ) values ( '") 243 .append(tableName).append("'); "); 244 245 StringBuffer insTriggerQuery = new StringBuffer(); 246 insTriggerQuery.append(" Create trigger ") 247 .append(RepConstants.getInsertTriggerName(tableName)) 248 .append(" after insert on ").append(tableName) 249 .append( 250 " Referencing new as newRow For each Row MODE DB2SQL begin ATOMIC ") 251 .append(insertLogTable).append(" Insert Into ") 252 .append(shadowTableName).append(" ( ") 253 .append(RepConstants.shadow_common_id2).append(", ") 254 .append(RepConstants.shadow_operation3).append(", ") 255 .append(RepConstants.shadow_status4).append(", ") 256 .append(colNameSeq).append(primColumnNamesSeq) 257 .append(RepConstants.shadow_serverName_n) 258 .append(" ) Values ( null , 'I' , null , ") 259 .append(colNameSeqPrefixNewRow).append(primColNameSeqPrefixNewRow) 260 .append("'").append(serverName).append("') ; end "); 261 262 StringBuffer delTriggerQuery = new StringBuffer(); 263 delTriggerQuery.append(" Create trigger ") 264 .append(RepConstants.getDeleteTriggerName(tableName)) 265 .append(" after delete on ").append(tableName) 266 .append( 267 " Referencing old as oldRow For each Row MODE DB2SQL begin ATOMIC ") 268 .append(insertLogTable).append(" Insert Into ") 269 .append(shadowTableName).append(" ( ") 270 .append(RepConstants.shadow_common_id2).append(", ") 271 .append(RepConstants.shadow_operation3).append(", ") 272 .append(RepConstants.shadow_status4).append(", ") 273 .append(colNameSeq).append(primColumnNamesSeq) 274 .append(RepConstants.shadow_serverName_n) 275 .append(" ) Values ( null , 'D' , null , ") 276 .append(colNameSeqPrefixOldRow).append(primColNameSeqPrefixOldRow) 277 .append("'").append(serverName).append("') ; end "); 278 279 StringBuffer updTriggerQuery = new StringBuffer(); 280 updTriggerQuery.append(" Create trigger ") 281 .append(RepConstants.getUpdateTriggerName(tableName)) 282 .append(" after update on ").append(tableName) 283 .append(" Referencing new as newRow old as oldRow For each Row MODE DB2SQL begin ATOMIC ") 284 .append(" declare maxlogid bigint; pkchanged char(1); ").append(insertLogTable) 285 .append(" set (maxlogid) = (select max(" + 286 RepConstants.logTable_commonId1 + ") from ") 287 .append(log_Table).append("); Insert Into ") 288 .append(shadowTableName).append(" ( ") 289 .append(RepConstants.shadow_common_id2).append(", ") 290 .append(RepConstants.shadow_operation3).append(", ") 291 .append(RepConstants.shadow_status4).append(", ") 292 .append(colNameSeq).append(primColumnNamesSeq) 293 .append(RepConstants.shadow_serverName_n) 294 .append(" ) Values ( maxlogid , 'U' , 'B' , ") 295 .append(colNameSeqPrefixOldRow).append(primColNameSeqPrefixOldRow) 296 .append("'").append(serverName).append("') ; ") 297 .append(" if( "); 298 for (int i = 0; i <primOldCols.length ; i++) { 299 if(i!=0) 300 updTriggerQuery.append(" and "); 301 updTriggerQuery.append(primOldCols[i]) 302 .append("!=") 303 .append(primNewCols[i]); 304 } 305 updTriggerQuery.append(" ) then pkchanged='Y'; end if; ") 306 .append(" Insert Into ") 307 .append(shadowTableName).append(" ( ") 308 .append(RepConstants.shadow_common_id2).append(", ") 309 .append(RepConstants.shadow_operation3).append(", ") 310 .append(RepConstants.shadow_status4).append(", ") 311 .append(colNameSeq).append(primColumnNamesSeq) 312 .append(RepConstants.shadow_serverName_n).append(" , ") 313 .append(RepConstants.shadow_PK_Changed) 314 .append(" ) Values ( maxlogid , 'U' , 'A' , ") 315 .append(colNameSeqPrefixNewRow).append(primColNameSeqPrefixOldRow) 316 .append("'").append(serverName).append("',pkchanged) ; end "); 317 try { 318 runDDL(pubsubName, insTriggerQuery.toString()); 319 } 320 catch (RepException ex) { 321 throw ex; 322 } 323 catch (SQLException ex) { 324 // ex.printStackTrace(); 325 // Ignore Exception 326 } try { 327 runDDL(pubsubName, delTriggerQuery.toString()); 328 } 329 catch (RepException ex) { 330 throw ex; 331 } 332 catch (SQLException ex) { 333 // ex.printStackTrace(); 334 // Ignore Exception 335 } 336 try { 337 runDDL(pubsubName, updTriggerQuery.toString()); 338 } 339 catch (RepException ex) { 340 throw ex; 341 } 342 catch (SQLException ex) { 343 // ex.printStackTrace(); 344 // Ignore Exception 345 } 346 347 } 348 349 public boolean isDataTypeOptionalSizeSupported(TypeInfo typeInfo) { 350 int sqlType = typeInfo.getSqlType(); 351 String typeName = typeInfo.getTypeName(); 352 switch (sqlType) { 353 case 4: 354 case 5: 355 case 7: 356 case -4: 357 case -5: 358 case 91: 359 case 92: 360 case 93: 361 case 8: 362 case -1: 363 case -7: 364 case 16: 365 return false; 366 default: 367 return true; 368 } 369 } 370 371 public void setTypeInfo(TypeInfo typeInfo, ResultSet rs) throws RepException, 372 SQLException { 373 switch (typeInfo.getSqlType()) { 374 case Types.BIT: 375 typeInfo.setTypeName("SMALLINT"); 376 break; 377 //-7 378 case Types.TINYINT: 379 typeInfo.setTypeName("SMALLINT"); 380 break; //-6; 381 case Types.SMALLINT: 382 typeInfo.setTypeName("SMALLINT"); 383 break; // 5; 384 case Types.INTEGER: 385 typeInfo.setTypeName("INTEGER"); 386 break; // 4; 387 case Types.LONGVARBINARY: 388 typeInfo.setTypeName("BLOB"); 389 break; // -4; 390 case Types.BIGINT: 391 typeInfo.setTypeName("BIGINT"); 392 break; // //-5; 393 case Types.FLOAT: 394 typeInfo.setTypeName("DOUBLE"); 395 break; // // 6; 396 case Types.REAL: 397 typeInfo.setTypeName("REAL"); 398 break; // // 7; 399 case Types.DOUBLE: 400 typeInfo.setTypeName("DOUBLE"); 401 break; // // 8; 402 case Types.NUMERIC: 403 typeInfo.setTypeName("DECIMAL"); 404 break; // // 2; 405 case Types.DECIMAL: 406 typeInfo.setTypeName("DECIMAL"); 407 break; // // 3; 408 case Types.CHAR: 409 if (typeInfo.getTypeName().equalsIgnoreCase("GRAPHIC")) { 410 typeInfo.setTypeName("CLOB"); 411 typeInfo.setSqlType(Types.CLOB); 412 return; 413 } 414 typeInfo.setTypeName("CHAR"); 415 break; // 1; 416 case Types.VARCHAR: 417 418 //done by nancy to handle postgreSQL to db2 419 if (typeInfo.getTypeName().equalsIgnoreCase("text")) { 420 typeInfo.setTypeName("CLOB"); 421 typeInfo.setSqlType(Types.CLOB); 422 return; 423 } 424 typeInfo.setTypeName("VARCHAR"); 425 break; // //12; 426 case Types.LONGVARCHAR: 427 if (typeInfo.getTypeName().equalsIgnoreCase("LONG VARGRAPHIC") || 428 typeInfo.getTypeName().equalsIgnoreCase("VARGRAPHIC")) { 429 typeInfo.setTypeName("CLOB"); 430 typeInfo.setSqlType(Types.CLOB); 431 return; 432 } 433 typeInfo.setTypeName("LONG VARCHAR"); 434 break; // //-1; 435 case Types.DATE: 436 typeInfo.setTypeName("DATE"); 437 break; // //91; 438 case Types.TIME: 439 typeInfo.setTypeName("TIME"); 440 break; // //92; 441 case Types.TIMESTAMP: 442 typeInfo.setTypeName("TIMESTAMP"); 443 break; // 93; 444 case Types.BINARY: 445 typeInfo.setTypeName("BLOB"); 446 break; //-2 447 case Types.BLOB: 448 typeInfo.setTypeName("BLOB"); 449 break; //2004 450 case Types.CLOB: 451 if (typeInfo.getTypeName().equalsIgnoreCase("DBCLOB")) { 452 typeInfo.setTypeName("CLOB"); 453 typeInfo.setSqlType(Types.CLOB); 454 return; 455 } 456 typeInfo.setTypeName("CLOB"); 457 break; //2005 458 case Types.VARBINARY: 459 typeInfo.setTypeName("BLOB"); 460 break; //-3 461 case Types.REF: //2006; 462 case Types.JAVA_OBJECT: //2000 463 case Types.NULL: //0 464 case Types.DISTINCT: //2001; 465 case Types.STRUCT: //2002; 466 case Types.ARRAY: //2003; 467 case Types.DATALINK: //70; 468 case Types.BOOLEAN: //16; 469 // case Types.BIT: //-7 470 default: 471 throw new RepException("REP031", new Object[] {typeInfo.getTypeName()}); 472 } 473 } 474 475 public AbstractColumnObject getColumnObject(TypeInfo typeInfo) throws 476 RepException { 477 int sqlType = typeInfo.getSqlType(); 478 switch (sqlType) { 479 case 1: // CHAR 480 case 12: //VARCHAR 481 return new StringObject(sqlType, this); 482 // case -4: // LONG VARCHAR FOR BIT DATA 483 // return new BytesObject(sqlType,this); 484 case 4: // INT 485 case 5: // SMALLINT 486 case -7: 487 case -6: //TINYINT 488 return new IntegerObject(sqlType, this); 489 case -5: // BIGINT 490 return new LongObject(sqlType, this); 491 case 3: // DECIMAL 492 case 8: // DOUBLE PRECISION 493 case 2: // NUMERIC 494 case 6: // FLOAT 495 return new DoubleObject(sqlType, this); 496 case 7: // REAL 497 return new FloatObject(sqlType, this); 498 case 91: // DATE 499 return new DateObject(sqlType, this); 500 case 92: // TIME 501 return new TimeObject(sqlType, this); 502 case 93: // TIMESTAMP 503 return new TimeStampObject(sqlType, this); 504 case 2004: 505 case -3: 506 case -4: 507 case -2: 508 return new BlobObject(sqlType, this); 509 case 2005: //clob 510 case -1: //LONG VARCHAR 511 512 /*used ClobStreamObject instead of ClobObject as clob was not synchronized with postgreSQl 513 --done by nancy*/ 514 return new ClobStreamObject(sqlType, this); 515 default: 516 throw new RepException("REP031", new Object[] {typeInfo.getTypeName()}); 517 } 518 } 519 520 public boolean isPrimaryKeyException(SQLException ex) throws SQLException { 521 if (ex.getSQLState().equalsIgnoreCase("23505")) { 522 return true; 523 } 524 else { 525 return false; 526 } 527 } 528 529 public int getAppropriatePrecision(int columnSize, String datatypeName) { 530 //System.out.println(" datatypeName ="+datatypeName+" columnSize ="+columnSize); 531 if (datatypeName.equalsIgnoreCase("numeric") && columnSize > 31) { 532 columnSize = 31; 533 } 534 else if (datatypeName.equalsIgnoreCase("DECIMAL") && columnSize > 31) { 535 columnSize = 31; 536 } 537 else if (datatypeName.equalsIgnoreCase("text") && columnSize == -1) { 538 columnSize = 3996; 539 } 540 return columnSize; 541 } 542 543 public void makeProvisionForLOBDataTypes(ArrayList dataTypeMap) { 544 ArrayList removeKeysList = null; 545 for (int i = 0, size = dataTypeMap.size(); i < size; i++) { 546 ColumnsInfo ci = (ColumnsInfo) dataTypeMap.get(i); 547 String dataType = ci.getDataTypeDeclaration(); 548 if (dataType.indexOf("BLOB") != -1 || 549 dataType.indexOf("CLOB") != -1) { 550 if (removeKeysList == null) { 551 removeKeysList = new ArrayList(); 552 } 553 removeKeysList.add(ci); 554 } 555 } 556 if (removeKeysList != null) { 557 for (int i = 0, length = removeKeysList.size(); i < length; i++) { 558 dataTypeMap.remove(removeKeysList.get(i)); 559 } 560 } 561 } 562 563 protected void createIndex(String pubsubName, String tableName) throws 564 RepException { 565 StringBuffer createIndexQuery = new StringBuffer(); 566 // create index ind on cmsadm2.R_S_Bank(Rep_sync_id); 567 createIndexQuery.append("create index ") 568 .append(RepConstants.Index_Name(tableName)) 569 .append(" on ") 570 .append(tableName) 571 .append("(") 572 .append(RepConstants.shadow_sync_id1) 573 .append(")"); 574 //System.out.println(" createIndexQuery : "+createIndexQuery.toString()); 575 try { 576 runDDL(pubsubName, createIndexQuery.toString()); 577 } 578 catch (RepException ex) { 579 // Ignore the Exception 580 } 581 catch (SQLException ex) { 582 // Ignore the Exception 583 } 584 } 585 586 public int getAppropriateScale(int columnScale) throws RepException { 587 if (columnScale < 0) { 588 throw new RepException("REP026", new Object[] {"1", "31"}); 589 } 590 else if (columnScale >= 31) { 591 columnScale = 31; 592 } 593 else if (columnScale >= 0 && columnScale < 31) 594 columnScale = columnScale; 595 return columnScale; 596 } 597 598 public PreparedStatement makePrimaryPreperedStatement(Connection pub_sub_Connection, String[] 599 primaryColumns, String shadowTable, String local_pub_sub_name) throws 600 SQLException, RepException { 601 StringBuffer query = new StringBuffer(); 602 query.append(" select * from "); 603 query.append(shadowTable); 604 query.append(" where "); 605 query.append(RepConstants.shadow_sync_id1); 606 query.append(" > "); 607 query.append("? "); 608 for (int i = 0; i < primaryColumns.length; i++) { 609 query.append(" and "); 610 query.append(primaryColumns[i]); 611 query.append("= ? "); 612 } 613 query.append(" order by " + RepConstants.shadow_sync_id1 + 614 " FETCH FIRST ROW ONLY "); 615 return pub_sub_Connection.prepareStatement(query.toString()); 616 } 617 618 public boolean isForeignKeyException(SQLException ex) throws SQLException { 619 if (ex.getErrorCode() == -204) 620 return true; 621 else 622 return false; 623 624 } 625 626 protected void createIgnoredColumnsTable(String pubName) throws SQLException, 627 RepException { 628 StringBuffer ignoredColumnsQuery = new StringBuffer(); 629 ignoredColumnsQuery.append(" Create Table ").append(getIgnoredColumns_Table()). 630 append(" ( ") 631 .append(RepConstants.ignoredColumnsTable_tableId1).append( 632 " bigint not null, ") 633 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2).append( 634 " varchar(255) , ") 635 .append(" Primary Key (").append(RepConstants. 636 ignoredColumnsTable_tableId1).append( 637 " , ") 638 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2).append( 639 " ) ) "); 640 runDDL(pubName, ignoredColumnsQuery.toString()); 641 } 642 643 protected void createTrackReplicationTablesUpdationTable(String pubSubName) throws 644 RepException, SQLException { 645 StringBuffer trackRepTablesUpdationQuery = new StringBuffer(); 646 trackRepTablesUpdationQuery.append(" CREATE TABLE ").append( 647 getTrackReplicationTablesUpdation_Table()).append(" ( " + 648 RepConstants.trackUpdation + " SMALLINT NOT NULL PRIMARY KEY) "); 649 runDDL(pubSubName, trackRepTablesUpdationQuery.toString()); 650 runDDL(pubSubName, 651 "Insert into " + getTrackReplicationTablesUpdation_Table() + " values(1)"); 652 } 653 654 //implement this method for providing provision to stop updations done on shadow table 655 protected void createTriggerForTrackReplicationTablesUpdationTable(String 656 pubSubName) throws RepException, SQLException { 657 /* StringBuffer trackRepTablesUpdationTriggerQuery = new StringBuffer(); 658 trackRepTablesUpdationTriggerQuery.append(" CREATE TRIGGER TRI_") 659 .append(getTrackReplicationTablesUpdation_Table()).append( 660 " ON " + getTrackReplicationTablesUpdation_Table()) 661 .append(" AFTER INSERT AS DELETE FROM " + 662 getTrackReplicationTablesUpdation_Table() + " WHERE ") 663 .append(RepConstants.trackUpdation + " NOT IN(SELECT * FROM inserted)"); 664 runDDL(pubSubName, trackRepTablesUpdationTriggerQuery.toString());*/ 665 } 666 667 public PreparedStatement makePrimaryPreperedStatementBackwardTraversing(String[] primaryColumns, long lastId, String local_pub_sub_name, String shadowTable) throws SQLException, RepException { 668 StringBuffer query = new StringBuffer(); 669 query.append(" select top 1 * from ") 670 .append(shadowTable) 671 .append(" where ") 672 .append(RepConstants.shadow_sync_id1) 673 .append(" < ? ") 674 .append(" and ") 675 .append(RepConstants.shadow_sync_id1) 676 .append(" > ") 677 .append(lastId); 678 for (int i = 0; i < primaryColumns.length; i++) { 679 query.append(" and ") 680 .append(primaryColumns[i]) 681 .append(" = ? "); 682 } 683 query.append(" order by ") 684 .append(RepConstants.shadow_sync_id1) 685 .append(" FETCH FIRST ROW ONLY ") 686 .append(" desc "); 687 log.debug(query.toString()); 688 //System.out.println("DB2Handler makePrimaryPreperedStatementDelete :: " +query.toString()); 689 Connection pub_sub_Connection = connectionPool.getConnection(local_pub_sub_name); 690 return pub_sub_Connection.prepareStatement(query.toString()); 691 } 692 693 /** 694 * isSchemaSupported 695 * 696 * @return boolean 697 */ 698 public boolean isSchemaSupported() { 699 return true; 700 } 701 702 }
Sponsors
