06apr05

55 %
45 %
Information about 06apr05
Entertainment

Published on November 28, 2007

Author: worm

Source: authorstream.com

Key Features in IDS Version 10.00 Compared with IDS 9.40 :  Key Features in IDS Version 10.00 Compared with IDS 9.40 Jerry Keesee, Director of the Informix Lab Jonathan Leffler STSM, Informix Database Engineering Information Management Division Agenda:  Agenda IDS Version 10.00 Features Summary Performance Autonomics, Ease of Administration Security Availability, ER Application Development, Standards Announcing the Availability of the V10 Release:  Announcing the Availability of the V10 Release Strong IBM Information Management commitment to the IDS product line. Most industry proven, reliable, and high performing OLTP RDBMS for Open Systems. Significantly raises the bar for autonomics, embeddability, availability and low-cost operations. Most accessible platforms on the market with Unix, Windows and Linux. Highest quality and robustness suitable for mission-critical, bet your business operations for SMB and enterprises world-wide. Generally available since end of February 2005. IDS: A Tradition of Innovation:  IDS: A Tradition of Innovation Object Relational Capabilities Enterprise Replication High Availability Data Replication DSA Architecture Configurable Page Size Online Create/Drop Index 4GL/EGL 1994 DataBlades Data Fragmentation Online Backup Parallel Data Query Java in the Server Smart Large Objects (Blobs/Clobs) Distributed High Availability (ER+HDR) Distributed Query (ISTAR) 2000 2004 Smart Restore (TLPIT) VLDB – Large Chunks 1998 Slide5:  “To sum it all up, IDS v10.0 is a DBA’s dream come true.” Gary Ben-Israel CIO, National Institute for Testing and Evaluation Jerusalem, Israel Beta Customer 12/04 IBM Informix Dynamic Server Roadmap :  1H 2008 DB2 Interoperability Market Technology Customer Requests 1Q 2003 Backup & Restore HDR & ER coexistence Remove size restrictions (Large File / Chunks) Security Sys. (LDAP / Encryption Monitoring, Utilities Performance IBM Informix Dynamic Server Roadmap 2004 2005 2006 2007 2008 2003 IDS vNext IDS v9.40 IDS v10 IDS vNext+ 2H 2006 Smooth Upgrade of HDR Online table re-org Dynamic Reconfiguration Auto Update Stats Auto Disk Mgmt RAS Autonomics 1Q 2005 4GL/WebSphere EGL .Net Native Provider JDBC 3.0 Compliance Column Level Encryption Default Roles Table Level PIT Restore Configurable Page Size ER Alter, DRAUTO, Resync Slide7:  IDS 10.00: Summary of Key Features Safety / High Availability / Reliability Table Level Point in Time Restore Faster Restart Query Plan and Tracing Tool Enhancements HDR Ease of Use Enhancements Partner Enhancements Configurable Page Size Larger Index Keys Create / Drop Index Online Single or Privileged User Admin Mode Rename DBSpace Default Roles Security Column Level Encryption LDAP Support for Windows Ease of Administration Automated Re-Sync of Tables in ER BAR Ease of Use Enhancements ER Templates / Ease Setup in ISA External Optimizer Directives Dynamically Set OPTCOMPIND ON-Tape Without Involving Backup Media App Development / SWG Integration 4GL / EGL Merge with WebSphere .Net Native Provider Visual Studio Integration JDBC 3.0 Standards Compliance Cross-DB Support for Built-In UDTs DRDA in ESQL/C & Server WORF and TDPI Bundled Performance & Infrastructure Query Processing and Load Performance Improvement Shared Memory Segments larger than 4GB Slide8:  Topics: Performance Enhancements Engine Impact hi low Number of times requested by users hi Configurable page sizes External optimizer directives Memory to non-PDQ queries Multiple fragments in single dbspace Dynamic OPTCOMPIND Performance: Configurable Page Size:  Performance: Configurable Page Size Reasons for Configurable Page Size Space efficiency Larger pages up to 16K bytes contiguous space Increased maximum key size Longer keys up to 3K bytes Access efficiency - fewer I/O operations for data and indices Performance: Configurable Page Size:  Performance: Configurable Page Size Space efficiency – larger pages up to 16K bytes contiguous space A row size of 1200 bytes: 1 row fits on a 2k page (6k every 3 rows). 3 rows fit on a 4k page, a savings of 33% For thirty 1200-byte rows: A 2k page size requires 60k. A 4k page size would require only 40k. A 6k page size requires just 36k, a 40% savings row1 row2 page header slot table 2048 (28) ---------- 2020 most any page type Performance: Configurable Page Size:  Performance: Configurable Page Size Increased maximum key size - longer keys up to 3K bytes Placing more keys on a page, we support longer keys without drastically increasing index level depth. The pre-10.0 key size limit was also a roadblock to utilizing the UNICODE character set, which caused some key values to exceed the max length. row1 row2 page header index page slot table Performance: Configurable Page Size:  Performance: Configurable Page Size Access efficiency Fewer I/O operations for data and indices. Long rows put into a single page benefits data access times by decreasing the number of pages read per row. Pages large enough to fit “oversize” rows eliminate the overhead of access time for remainders pages. More items put on a larger index page and a decreased number of levels in a btree index reduce index traversal costs. For DSS environments, using larger data pages that maximize disk scan rates may improve table scan performance. Performance: Configurable Page Size:  Performance: Configurable Page Size Larger page sizes need larger rows. Otherwise, you waste space: Still 255 rows per page. Applies to indexes as well as data If the key size is too small, you waste space in the large pages. RS =  ((PS × 1024 – 28) ÷ 255)  – 4 PS × 1024 > (RS + 4) × 255 + 28 page overhead slot table entry Performance: Configurable Page Size:  Performance: Configurable Page Size Create new dbspaces with non-default page size onspaces –c –d dbspace_8k –p \   /informix/dev/sys_13.dbspace_8k.c0 \   –o 0 –s 2048000 –k 8 Note: all critical dbspaces must use basic page size. rootdbs dbspaces containing logical logs dbspace containing physical log If no buffer pool of this page size exists A new buffer pool is created Using default buffer pool configuration. Performance: BUFFERPOOL configuration:  Performance: BUFFERPOOL configuration Old, familiar parameters are technically obsolete: BUFFERS, LRUS, LRU_MIN_DIRTY, LRU_MAX_DIRTY Use new, repeatable BUFFERPOOL parameter: BUFFERPOOL size=2K buffers=3000000 lrus=128 lru_min_dirty=0.01 lru_max_dirty=0.05 Separated by commas, all on one line. BUFFERPOOL size=2K,buffers=3000000,lrus=128,lru_min_dirty=0.01,lru_max_dirty=0.05 Also ‘default’ instead of ‘size=nk’ Performance: BUFFERPOOL configuration:  Performance: BUFFERPOOL configuration Each page size has its own buffer pool. Different buffer pool settings for each BUFFERPOOL. If you add a new dbspace with a non-default page size and no buffer pool exists for that page size IDS allocates a new buffer pool using the default settings. If there is no BUFFERPOOL default in $ONCONFIG, Values from onconfig.std are used instead. And there are default defaults for the default buffer pool. BUFFERPOOL entries in ONCONFIG are allocated Even if no dbspace currently uses that page size. Performance: BUFFERPOOL configuration:  Performance: BUFFERPOOL configuration Consider using (say) 12K pages for temporary dbspaces Configure the 12K buffer pool for temporary tables: With high LRU_MIN_DIRTY, LRU_MAX_DIRTY You don’t need temporary data written to disk Consider using (say) 16K pages for the major table It gets its own buffer pool all to itself. Beware being too clever! IDS does a good job balancing the use of buffers. Performance: Non-PDQ queries – a problem…:  PDQPRIORITY set? Performance: Non-PDQ queries – a problem… Performance: Memory Allocation for non-PDQ Queries:  Performance: Memory Allocation for non-PDQ Queries Feature was first available in 9.40.xC4. You can specify how much memory is allocated to non-PDQ queries. The default of 128K can be insufficient for queries that specify ORDER BY, GROUP BY, hash joins, or other memory-intensive options. Use the new configuration parameter, DS_NONPDQ_QUERY_MEM, to specify more memory than the 128K that is allocated to non-PDQ queries by default. The onstat, onmode, and onmonitor utilities support this configuration parameter. Performance: External Optimizer Directives:  Performance: External Optimizer Directives Suppose you purchase an application But you do not get the source code for it. Suppose some of its queries work slowly Because of a peculiarity in the optimizer, statistics, … Suppose that an optimizer hint can fix the problem. Before version 10.00, there was no way to add a hint. Unless you used I-Spy. With version 10.00, you can store external directives. Performance: External Optimizer Directives:  Performance: External Optimizer Directives New SQL statement, SAVE EXTERNAL DIRECTIVES Creates and registers external optimizer directives. Stored in the new sysdirectives system catalog table. At run-time, external directives have to be enabled: Per session by the new environment variable: export IFX_EXTDIRECTIVES=1 Enabled for client unless server disables it 0 => disabled for this client regardless of server setting. Or per instance by the new $ONCONFIG parameter: EXT_DIRECTIVES 1 # Enable external directives Enabled for client if explicitly requested 2 => enabled unless explicitly disabled by client. 0 => disabled for all users regardless of client setting. Multiple Fragments of a Table in One Dbspace:  Multiple Fragments of a Table in One Dbspace Suppose you wanted to fragment a table with: One fragment a quarter for two years of data. Eight fragments in total. You needed at least 8 dbspaces for this: Each fragment had to go into a separate dbspace. With version 9.40 and large chunks, It is better to have one chunk per disk drive. But you only have one 80 GB drive. Multiple Fragments of a Table in One Dbspace:  Multiple Fragments of a Table in One Dbspace You can create partitions within a dbspace that can each support a table fragment. Reduces the total number of dbspaces needed for a fragmented table. Storing multiple table fragments in a single dbspace improves query performance over storing each fragmented expression in a different dbspace. This feature improves performance and simplifies management of dbspaces Multiple Fragments of a Table in One Dbspace:  Multiple Fragments of a Table in One Dbspace CREATE TABLE Example1 (…table definition…)  FRAGMENT BY EXPRESSION  PARTITION p1_ex1 (…) IN dbspace1,  PARTITION p2_ex1 (…) IN dbspace1,  PARTITION p3_ex1 (…) IN dbspace1,  REMAINDER PARTITION pr_ex1 IN dbspace1; Upwardly compatible: Old scheme had each partition anonymous in a separate dbspace. New scheme allocates partition name same as dbspace name for upgraded fragmented tables. Performance: OPTCOMPIND:  Performance: OPTCOMPIND response time think/optimization time query/execution time OPTCOMPIND Setting 0 1 2 use index(es) if RR then 0; else 2 use lowest cost* * cost = I/O + (cpu *0.03) good indexes; healthy stats bad indexes; stale stats healthy stats A Review Performance: Dynamic OPTCOMPIND:  Performance: Dynamic OPTCOMPIND You can use SET ENVIRONMENT OPTCOMPIND to set OPTCOMPIND environment variable dynamically for the current session. The value that you enter using this statement takes precedence over the current setting specified in the ONCONFIG file. The default setting of the OPTCOMPIND environment variable is restored when your current session terminates. No other user sessions are affected by SET ENVIRONMENT OPTCOMPIND statements that you execute. Slide27:  Topics: Administration Enhancements Engine Impact hi low Number of times requested by users hi Tablespace tablespace management True single user mode Default roles Renaming dbspaces Shared memory segments bigger than 4GB HDR setup with EBR Better event alarms information Viewing ON-Bar logical log info More comprehensive version information ON-Tape using standard i/o Enhanced ON-Bar debugging ON-Tape Using Standard I/O:  ON-Tape Using Standard I/O ON-Tape can use standard I/O channels Instead of a tape device or disk file. Set TAPEDEV, LTAPEDEV to STDIO. Now ontape can use pipes for archives and restores. Process the data with other programs, Without first saving the data in files or tape devices. For example: Use compression to save media space. Use cloning to duplicate the archive for safety. Use encryption to prevent casual snooping on archives. But compress before encrypting when saving. Watch the key management! Set up HDR more quickly: Restore the data directly to the secondary server. Skip the step of saving the data to file or tape. Shared Memory Segments Bigger Than 4 GB:  Shared Memory Segments Bigger Than 4 GB On 64-bit platforms – only. Shared memory segments can be as large as: Your operating system platform allows, Which is controlled by the SHMMAX kernel parameter. Important for systems with large main memories. Do not want 256 shared memory segments On a machine with 1TB main memory for use by IDS. Administration: Renaming Dbspaces:  Administration: Renaming Dbspaces Existing customers who wish to reorganize their data are moving their data to a new dbspace, then reloading the data back into the original dbspace to regain the original dbspace name. The last reload step could be avoided by using a rename dbspace option. Helps in recycling dbspaces. Time consuming operations such as reorganizing the data in an existing dbspace can benefit from this feature. The rename dbspace operation only changes the dbspace name: It does not reorganize data Administration: Renaming Dbspaces:  Administration: Renaming Dbspaces Feature was added to IDS 9.40.UC3, with limitations: Cannot rename: blobspaces, sbspaces, temporary, or external spaces. Cannot rename dbspaces referenced by: DBSPACETEMP, CDR_DBSPACE SBSPACENAME, SBSPACETEMP SYSSBSPACENEAME, CDR_QHDR_DBSPACE CDR_QDATA_SBSPACE, Cannot rename dbspaces if you are using HDR. Cannot rename dbspaces when ER is active. Administration: Renaming Dbspaces:  Administration: Renaming Dbspaces In IDS version 10.00, some restrictions remain: Rename can not be done on critical spaces Root dbspace Dbspace containing physical log or logical logs. A dbspace with down chunks can not be renamed. ON-Monitor cannot rename spaces. Take a Level 0 archive of the renamed space and root dbspace after renaming. Otherwise, recovery from archive uses the old dbspace name. Rename on HDR primary propagates to secondary. Administration: Default Roles:  Administration: Default Roles Suppose you manage your database permissions using roles. You use an application with no support for roles. You don’t have the source code, so you can’t fix it. Prior to IDS version 10.00, you were stuck. With IDS version 10.00, you can create a role And assign that as the default role to Individual users Or to PUBLIC. Administration: Default Roles:  Administration: Default Roles GRANT DEFAULT ROLE z_role TO zaphod; Zaphod is granted permission to use z_role. If he does not already have that permission. When Zaphod connects to the database, The current role is automatically set to z_role. The role can be changed after connecting: SET ROLE NONE; SET ROLE totherone; SET ROLE DEFAULT; It does not limit the user If the users knows what they’re doing. Administration : Managing the Tblspace Tblspace:  Administration : Managing the Tblspace Tblspace The DBSA adds a chunk temporarily to a dbspace To create a large table in the dbspace. During processing the tblspace tblspace extends Into the newly added chunk. The DBSA drops the large table And now wants to drop the chunk. IDS won’t let that happen! Administration : Managing the Tblspace Tblspace:  Administration : Managing the Tblspace Tblspace Tblspace == partition TBLspace TBLspace Special table that tracks other tables within a dbspace. Every dbspace has its own tblspace tblspace That tracks tables (partitions) within its own dbspace. There is only one tblspace tblspace per dbspace. But as with any tblspace it can have multiple extents. Partnum is 0xDDD00001, where DDD = dbspace number. Resolution Options Prior to IDS 10.00:  Resolution Options Prior to IDS 10.00 Drop Dbspace This causes the customer to have to unload the entire dbspace. And drop all tables in the dbspace. Then drop and recreate the dbspace. The rebuild all the tables in the recreated dbspace. This creates an outage! Dial-in To Drop Chunk Another solution was for Advanced Support dial in and drop the chunk. They would also clean up the tblspace tblspace. This caused down time and is risky (usually avoided). First and Next Extent Sizes – Root Dbspace:  First and Next Extent Sizes – Root Dbspace Root dbspace (oninit –i) Two new ONCONFIG parameters to configure root dbspace TBLTBLFIRST Specifies the size of the first extent of the tblspace tblspace TBLTBLNEXT Specifies the next extent size of the tblspace tblspace If these parameters are not present, the defaults will be used. All sizes are in KB, and must be a multiple of the basic page size. First and Next Extent Sizes – Non-root Dbspaces:  First and Next Extent Sizes – Non-root Dbspaces Non-root dbspace created with onspaces When creating a new dbspace with onspaces you will now be allowed to specify the first and next extent sizes for the tblspace tblspace. -ef <first extent size> –en <next extent size> If these options are not used the defaults will be used. Example onspaces –c –d dbs1 –p /spare2/dbs1.1 –o 0 \ –s 10000 –ef 150 –en 150 Administration : Single User Mode:  Administration : Single User Mode “Single-User Mode” feature allows the IBM Informix Dynamic Server to be put into a maintenance mode, allowing only the user ‘informix’ to connect the server. It is intended to be viewed as a mode intermediate between Quiescent mode and Online mode. It allows a DBA to have the server in a fully functional mode where any required maintenance may be performed. It is intended to be used by DBA’s to perform any SQL/DDL maintenance while preventing normal users from connecting. ISA also supports the Single-User Mode functionality. Slide41:  Topics : Security Enhancements Engine Impact hi low Number of times requested by users hi Restrict who can create external UDRs Secure environment check before startup Trigger introspection PAM authentication Column-Level Encryption Restrict who can create databases Security: PAM Authentication:  Security: PAM Authentication PAM – Pluggable Authentication Modules It is a standardized system for allowing the OSA (Operating System Admininistrator) to configure how authentication is done. Allows OSA to configure authentication methods. Available on Linux, AIX, Solaris, HP-UX and others. configured at the Operating System level. APIs to write shared object (.so) at wws.sun.com/software/solaris/pam PAM supports challenge-response protocols: In response to initial authentication request, PAM issues a challenge, And waits for response from application. /usr/lib/security PAM Modules /etc/pam/conf configuration file login auth required /usr/lib/security/pam_unix.so.1 Trigger Introspection:  Trigger Introspection In IDS before version 10.00, Use triggers to audit modify activity on tables. But each table needs its own logging routines Because the structure of each table is usually different. In IDS version 9.40.xC4 or later, You can create a single introspective routine in C: To audit many different tables. Obtain information about the triggered action: Triggers, triggering tables, views, statements, And the values of rows involved in the trigger actions. Not for the faint of heart. You create and install a new shared object. Restricting Registration of DataBlade Modules:  Restricting Registration of DataBlade Modules In IDS before version 10.00, Any DBA or RESOURCE level user can create UDRs Including EXTERNAL ones which load a shared library. Any of those users could subvert the system security. In IDS version 10.00, The DBSA can control who creates EXTERNAL UDRs. Set IFX_ EXTEND_ROLE 1 in ONCONFIG file Default is backwards compatible 0 or ‘off’ – insecure. Grant EXTEND role to selected users In each database where they must create the UDRs. GRANT EXTEND TO udrdef1; A DBA cannot grant the EXTEND role. Restricting Database Creation:  Restricting Database Creation Since IDS 9.30, ONCONFIG file can contain DBCREATE_PERMISSION Lists the user names permitted to create databases. Strongly recommended: Set it to a conservative value such as: DBCREATE_PERMISSION dba1,dba2,informix Only dba1, dba2 and informix can create databases. Suggestion – don’t set it to just informix Unless informix already owns all your databases, Which isn’t the best idea, but isn’t fatal either. Security: Column Level Encryption:  Security: Column Level Encryption IDS version 10.00 adds new SQL statement: SET ENCRYPTION PASSWORD ‘password’; And new encryption and decryption functions: ENCRYPT_AES, ENCRYPT_TDES DECRYPT_CHAR, DECRYPT_BINARY GETHINT Together, these permit applications to encrypt data in the columns. Discussed in the March ‘Chat With The Lab’ Not repeated here. Security: Secure Environment Check:  Security: Secure Environment Check Server utilities check that the environment is secure Before doing anything dangerous. In versions 7.31.UD7, 9.30.UC8, 9.40.UC3, and later. Public write permission is forbidden On both directories and files which are checked. For each key directory: $INFORMIXDIR itself, and Sub-directories bin, lib, etc, msg, gls, aaodir, dbssodir: Check that the directory exists. It is owned by user informix and the correct group. Security: Secure Environment Check:  Security: Secure Environment Check The permissions on the ONCONFIG file are correct. The file must belong to the DBSA group. Usually, the ONCONFIG file is owned by user informix. The permissions on the sqlhosts file are correct. Normally, the sqlhosts file is $INFORMIXDIR/etc/sqlhosts. The owner should be user informix. The group should be either informix or the DBSA group. Configuration filenames shorter than 256 characters. $INFORMIXDIR/etc/onconfig.std $INFORMIXDIR/etc/$ONCONFIG Hence, INFORMIXDIR shorter than 238 characters, Less if $ONCONFIG is longer than 12 characters. Topics : Availability Enhancements:  Topics : Availability Enhancements Engine Impact hi low Number of times requested by users hi Faster Recovery with Fuzzy Checkpoints Online Index Drop & Rebuild Table Level Restore Availability: Recovering Quickly with Fuzzy Checkpoints :  Availability: Recovering Quickly with Fuzzy Checkpoints Two new configuration parameters (FAST_RESTART_PHYSLOG and FAST_RESTART_CKPT_FUZZYLOG) reduces the time required for engine recovery. This supports high availability by improving recovery performance when using fuzzy checkpoints. Availability: Index Changes:  Availability: Index Changes CREATE INDEX and DROP INDEX now supports DDL operations that apply no exclusive lock to the table on which the specified index is defined. If you use this syntax to create an index on a table that other users are accessing, the index is not available until no user is updating the table. After you issue the new syntax to drop an index, no one can reference the index, but concurrent DML operations can use the index until they terminate. Dropping the index is deferred until no user is using the index. This feature maintains the availability of the table within a production environment after an existing index has ceased to be efficient. Availability: Point-in-Time Table Restore:  Availability: Point-in-Time Table Restore Purpose: to provide the customer with the ability to easily extract a set of tables, a table or a portion of a table from a level 0 archive to a user specified point in time. The extracted data can be placed in an external table or on a table on the server of the user’s choice regardless of server version or machine type as long as the database server is listed in the sqlhost file. Benefits to the DBA:  Benefits to the DBA SQL Driven Distributed Restore (SDDR) Extract a table or set of tables Data may be placed in the same version database or on a different database version with a different machine architecture You may apply a filter to the retrieved data Retrieve just a subset of the columns Repartitioning of the data Slide54:  Topics : Application Development/Standards Enhancements Engine Impact hi low Number of times requested by users hi ESQL/C to DB2 Support Server Studio Java Edition 4.1 included Support of Java Eclipse Framework Full Support of .NET JDBC 3.0 Spec Support Applications : JDBC 3.0 Support:  Applications : JDBC 3.0 Support Version 3.0 of the IBM Informix JDBC Driver supports the following features in compliance with the Sun Microsystems JDBC 3.0 specification: Internally update BLOB and CLOB data types using all methods introduced in the JDBC 3.0 specification. Retrieve auto-generated keys from the database server. In addition, J/Foundation supports JRE Version 1.4 and the JDBC 3.0 specification. Applications : Full .NET support :  Applications : Full .NET support The IBM Informix .NET Provider enables Windows .NET applications to access and manipulate data in IBM Informix databases. The IBM Informix .NET Provider is a runtime library that encapsulates a data access API for use by Microsoft .NET applications. It consists of a set of specialized classes that implement standard Microsoft ADO.NET interfaces and serves as a bridge between IBM Informix databases (data sources) and .NET applications. Windows client applications written in any .NET supported language can take advantage of the IBM Informix .NET Provider. Some examples of client applications are: Visual BASIC .NET applications Visual C# .NET applications Visual J# .NET applications ASP.NET web applications The IBM Informix .NET Provider a connection editor dialog box, a command editor dialog box, and a data adapter wizard as Microsoft Visual Studio add-ins. Applications: ESQL/C to DB2:  Applications: ESQL/C to DB2 You can run Informix ESQL/C applications with DB2 servers and databases. The Informix ESQL/C product provides a new library that is called when you use the esql command to preprocess your files to work with DB2. Informix ESQL/C runs with DB2 Version 8.2, or later, running on Linux, UNIX, and Windows operating systems. Slide58:  Topics : HDR/ER Enhancements Engine Impact hi low Number of times requested by users hi Easier ER setup with templates Seamless online resynchronization of ER nodes Resending indexes in HDR Automatic switchover of HDR servers (DRAUTO) ALTER of replicated tables HDR: Resending Indexes in HDR:  HDR: Resending Indexes in HDR You can resend an index that became corrupt on the secondary server in an HDR pair. Resending an index is quicker than dropping and then rebuilding the index on the primary server. This feature increases the availability of the HDR primary server. HDR: DRAUTO:  HDR: DRAUTO You can automate switching servers for High-Availability Data Replication if the primary server fails by using the DRAUTO configuration parameter. If DRAUTO is set to either RETAIN_TYPE or REVERSE_TYPE, the secondary database server switches to type standard automatically when an HDR failure is detected. RETAIN_TYPE, the original secondary database server switches back to type secondary when the HDR connection is restored. REVERSE_TYPE, the original secondary database server switches to type primary when the HDR connection is restored, and the original primary switches to type secondary. ER Enhancements: Alter table/fragment support:  ER Enhancements: Alter table/fragment support Overview This feature provides alter support for tables being replicated via Enterprise Replication. Currently, if the table schema needs to be altered or if the fragmentation strategy needs to be changed, then replication must be stopped, then alter is performed and then replication must be restarted. This is problematic as it makes it impossible to really consider ER in a 24X7 environment. List of supported alter operations 1. The ability to add/drop default values 2. The ability to add/drop SQL checks 3. The ability to add/drop fragments 4. The ability to attach/detach fragments 5. The ability to add/drop columns 6. The ability to recluster indexes 7. The ability to alter non replicated columns ER Enhancements: Alter table/fragment support:  ER Enhancements: Alter table/fragment support Alter mode Alter mode is a new state of a replicated table. DML operations are disallowed in this mode. DDL operations on a replicated table are allowed only while the table is in alter mode. Alter mode can be set/unset manually through CDR CLI or implicitly through SQL alter statement itself. Clients can alter the replicated table without placing the table in alter mode through ‘cdr alter …’ command. SQL layer will internally do a callback to ER to set/unset alter mode before and after performing the alter operation. One exception to this is ‘attach fragment’ scenario. ER Enhancements: Alter table/fragment support:  ER Enhancements: Alter table/fragment support Attach fragment scenario For attaching a new fragment, first DBA needs to place the replicated table in alter mode through CDR CLI interface, drop the primary key, attach the fragment, recreate the primary key then unset alter mode through CDR CLI interface. ER Enhancements: Alter table/fragment support:  ER Enhancements: Alter table/fragment support Mastered Replicates Currently no way to know if data types match between replicate nodes Provides data type checking to eliminate possibility of corruption New syscdr tables track data type information ER Enhancements: Alter table/fragment support:  ER Enhancements: Alter table/fragment support Remastering process Existing replicate can be redefined by “remastering the replicate”. Through remastering process, a new column can be added/dropped to/from a replicate definition. Also an existing non-mastered replicate can be converted to a mastered replicate using remastering process. A replicate can be remastered using two different procedures Auto remastering Auto remastering can be performed through “cdr remaster” command syntax for “cdr remaster” command cdr remaster –m <master server group name> <replicate name> <select statement> In order to perform auto remastering, mastered replicate definition must be defined with ‘name verification on’ attribute (--name y). That means, replicated table column names must match across all the replicate participants. ER Enhancements: Alter table/fragment support:  ER Enhancements: Alter table/fragment support Manual remastering procedure Create a shadow replicate with same attributes as primary replicate and with new select clause, swap the existing primary replicate and the newly created shadow replicate. Syntax for manual remastering procedure Syntax for shadow replicate creation: cdr define replicate –-mirrors <primary replicate name> shadow replicate name [participant definitions] Note: Select statements in the participant definitions can include newly added column(s) or can omit dropped column(s). Syntax for shadow replicate creation: cdr swap –c <server name> -p <primary replicate_name> -P <primary replicate id> -s <shadow replicate name> -S <shadow replicate id> After remastering is done, the old replicate definition will get cleaned up automatically once the replicate definition is no longer required. ER Enhancements: Alter table/fragment support:  ER Enhancements: Alter table/fragment support Steps required for adding a new column to a replicated table Perform alter operation and add the new column to the replicated table at all the participating nodes Remaster the replicate to include the newly added column to the replicate definition. Steps required for dropping a replicated column Remaster the replicate and remove the column(s) being dropped from the replicate definition. Wait for the shadow replicate(created as part of remastering process) to get cleaned up automatically. Perform the alter operation and drop the column(s) from the replicated table at all the participating nodes. ER Enhancements: Alter table/fragment support:  ER Enhancements: Alter table/fragment support Restrictions ER must be in active state for altering a replicated table except in scenarios where adding/dropping check constraints and default values. Alter operations are supported only on tables defined with mastered replicates. Altering a replicated column size or data type is not supported. (Note: Modifying a non replicated column is supported). Rename table operation is not supported Rename column operation is not supported Drop table operation is not supported ER Enhancements: Alter table/fragment support:  ER Enhancements: Alter table/fragment support Things to consider while altering a replicated table Have sufficient log space to avoid long transaction Have sufficient space in queue sbspace. Have sufficient number of locks Troubleshooting If the alter operation returns sql error code 19996 (can not unset alter mode): Check the server log file to get the exact ER error message and the corresponding error number. Correct the error. Unset alter mode manually through CDR command line interface. Note: Even though user gets SQL error code –19996, user transaction might have committed. The reason for this is, alter mode unset operation is attempted after completing(commit/rollback) the user transaction. ER Enhancements: Replicate Templates:  ER Enhancements: Replicate Templates Overview Ease of ER administration and setup The entire Enterprise Replication domain can be setup using simple commands with options such as defining replicates on all tables within database, specified on the command line or using a input file. A template can perform an initial data synchronization on new servers being added to a template.. A template can optionally create tables during realization if they do not exist on target servers during template realization. Eliminates most of the table Schema related errors Templates use the master dictionary from the Master node to create these tables to ensure consistent schemas between the nodes. ER Enhancements: Replicate Templates:  ER Enhancements: Replicate Templates Defining a template: cdr define template example1 -c detroit \ -C timestamp -S tran \ --master=chicago \ --database=new_cars table1 table2 table3 Template name of example1. Connection is made to the server detroit. Conflict-resolution rule is timestamp. Conflict-resolution scope is transaction. Master replicate information is on server chicago. Use the new_cars database. Include only the tables table1, table2, and table3. ER Enhancements: Replicate Templates:  ER Enhancements: Replicate Templates Another example of defining a template: cdr define template example1 -c detroit \ -C timestamp -S tran \ --master=chicago -–ignoredel y \ --database=new_cars -–file=tables.list Much of it the same as before. Specify --ignoredel so delete operations are not replicated. Retaining deleted rows on target servers is useful for consolidation models. Specify --file if the list of replicated tables is in a file. There are short-form options for the long ones: For example, --master is the same as –M. ER Enhancements: Replicate Resynchronization:  ER Enhancements: Replicate Resynchronization Overview Meant to be used to: Bring a newly participating table up-to-date with the ongoing replication Repair a replicated table if replication was stopped or failed for some reason. Two ways to repair a table: Quick way: By processing the ATS/RIS files. Other way: By defining and running a ‘resynch job’. ER Enhancements: Event Messages:  ER Enhancements: Event Messages In existing product (pre 10.0), all ER event alarms are identified with event class id 6 (ALRM_SUBS) and class message “internal subsystem failure”. This makes it hard to automate ER administrative work. IDS 10.00 introduces new ER class IDs for specific ER events Now clients can automate ER administrative tasks. For example, add a new chunk to the ER queue sbspace If the queue stable storage is full. IDUG / IIUG 2005 Conference May 22nd-26th – Denver Convention Center Denver, Colorado, USA http://www.iiug.org/conf :  IDUG / IIUG 2005 Conference May 22nd-26th – Denver Convention Center Denver, Colorado, USA http://www.iiug.org/conf Over 50 technical presentations dedicated to Informix All given either by Informix R&D staff or by fellow Informix Users Four Specialized Training Sessions: A Technical Deep Dive into IDS 10 Advanced IDS Setup from Disk to Engine Is Your DBA Paranoid Enough? Everything You Always Wanted to Know about Informix SQL Meet the Informix Developers and fellow Informix Users IIUG Annual General Meeting Largest gathering of Informix Users in the world in 2005! Completely run by the International Informix User Group To Register(*) visit: http://www.iiug.org/conf For more information see: http://www.iiug.org/news/insider/insider_mar05.html#2 http://conferences.idug.org/namerica/2005/index.cfm * Note: All registration is handled by IDUG – The International DB2 User Group (IDUG). Continued Focus on Quality:  Continued Focus on Quality #1 Theme = Customer / Partner Loyalty Demonstrated quality improvements compared to previous releases Results of numerous quality initiatives driven by customer feedback Significant reduction in backlog across all code-lines Significant improvement / expansion of test suites Improved TCO and usability Improved product reliability Improved application development capabilities Extended beta with excellent participation and feedback IDS v10 Platforms Support:  IDS v10 Platforms Support Sun HP Microsoft IBM SGI Intel OS Platform Solaris 8,9 Solaris 8,9 HP-UX 11iv1 HP-UX 11iv1 Win 2K Win 2003 AIX 5.2 AIX 5.2 IRIX 6.5.x Linux Platform SPARC SPARC PA-RISC PA-RISC Intel Intel Power Power MIPS x86 x86 32/64-bit 32-bit 64-bit 32-bit 64-bit 32-bit 32-bit 32-bit 64-bit 64-bit 32-bit 64-bit Platform Itanium 2 DEC Alpha Itanium 2 Win 2003 zSeries pSeries 64-bit HP-UX 11iv2 Tru64 5.1B 64-bit Linux Linux IDS Offerings / Bundles:  IDS Offerings / Bundles IDS Express (2 cpus) Linux & Windows IDS Workgroup Edition (up to 4 cpus, OK as ER leaf node) IDS Enterprise Edition IDS 7-to-9 Trade up Blue Bundle IDS 7-to-10 Trade up Helpful Links & Contacts:  Helpful Links & Contacts Informix Instant Answers by E-mail: mailto:ibmifmx@us.ibm.com Informix Product Page http://www.ibm.com/informix/ Informix Platform Roadmap http://www-306.ibm.com/software/data/informix/pubs/roadmaps.html Informix Product Life Cycle (PLC) Roadmap http://www-306.ibm.com/software/data/informix/support/plc/ Informix International User Group (IIUG) http://www.iiug.org DM Today Newsletter http://www-3.ibm.com/software/data/db2infonews/ Helpful Links & Contacts:  Helpful Links & Contacts

Add a comment

Related presentations

Related pages

VAPT M118 118i 06apr05 - Business Services and Digital ...

WorkCentre™ M118/118i Criteria Supporting Features Remarks and explanations Section 1194.21 Software Applications and Operating Systems Section 1194.22
Read more

Talking Paper IEC 06Apr05 - Office of the Under Secretary ...

Draft Deliberative Document – For Discussion Purposes Only – Do Not Release Under FOIA 1 6 April 2005 Talking Paper Subject: BRAC 2005 Red Team Meeting ...
Read more

Caserio Gourmet, Inc. 06-Apr-05 - Food and Drug Administration

April 6, 2005. VIA FEDERAL EXPRESS. Warniing Letter 05-ATL-12 . Rosana F. Silva, President Caselro Gourmet, Inc. 105 Hood Street, Bay #7 Durham, NC 27701-3704
Read more

HW8 (due 06Apr05) - The ball has just been released from ...

View Notes - HW8 (due 06Apr05) from BME 2210 at Georgia Tech. The ball has just been released from Jarret Jack's hands. The ball is increasing in height
Read more

HW9 (due 06Apr05) - BME 2200 (Spring 2005) HW 9 (due ...

View Notes - HW9 (due 06Apr05) from BME 2210 at Georgia Tech. BME 2200 (Spring 2005) HW 9 (due 06Apr05) (every student should turn in their own
Read more

OMH225687: 9560 STS Combine(9560 STS 720101- ), Block File ...

IMPORTANT: The grain tank capacity of the 9560 STS combine is 220 bushels. Severe damage to the combine structure and load-bearing components can occur if ...
Read more

CopyCentre™ C118 Digital Copier Voluntary Product ...

CopyCentre™ C118 Digital Copier Criteria Supporting Features Remarks and explanations Section 1194.21 Software Applications and Operating Systems
Read more

Dust and Haze Blow Across China: Natural Hazards : NASA ...

Blowing desert dust adds to existing haze in the eastern basin of China. The grayish haze spread across most of the center of the image is likely a mixture ...
Read more

Primary And Secondary Fuel Filter Element (8.1 L Engine ...

Primary And Secondary Fuel Filter Element (8.1 L Engine)-Replacement. CAUTION: High-pressure fluid remaining in fuel lines can cause serious injury.
Read more