Sunday, 23 October 2011

TIP: Configuring Notepad++ to use the XML lexer on any file type with XML content

Say you've inherited a build infrastructure which you must maintain which has many files with different file extensions but the file format for all files is XML. Some of these extensions could be legitimate .xml, .config and .xslt for example with other files having proprietary extensions such as .nant, .deploy, .dist and many more. The goal is not just to be able to double click on them to automatically open them in Notepad++ (simple), but to have Notepad++ open the file with the XML lexer so all those XML tokens come up nice and colourful for ease of reading / editing. This saves time and makes editing files much less error prone. So, how is this done? In two parts... This example has been done on 32-bit Windows XP and 64-bit Windows 7 with 32-bit Notepad++ 5.8.7 and higher.

Associating the file extensions with the Notepad++ application


First the easy part... For each distinct file extension, right click, select Open with, select Choose default program. If Notepad++ is not listed, click Browse and find the executable and select it and click Open. Ensure the Always use the selected program to open this kind of file check box is ticked. Click OK and voila! FYI: the association of an application with a file extension is stored in the Windows registry.

Associating any file extension with the Notepad++ XML lexer

Now, for the less obvious part... It took me a while to realise that you can tell Notepad++ what file extensions (both standard and proprietary) should use the XML lexer rather than than having to manually set it every time (annoying!) via the Notepad++ Languages menu. To achieve this, open Notepad++, select Settings from the menu bar and click on Style configurator... Under Language, select XML. Under this list is a text box called Default ext which lists what extensions currently use the XML lexer. Opposite that text box is another called Custom ext. Add your new extensions, such as .config, .nant, .deploy or .dist etc here - ensure they are space delimited excluding the dot / period so the list looks like config nant deploy dist and click OK. FYI: these changes are saved in Notepad++'s styles.xml file usually found under said programs application data location (default location is under Documents & Settings for XP and Users for Win7).

Now, whenever you double click on any .config, .xslt, .nant, .deploy or .dist file, it will be automatically opened in Notepad++ with the XML lexer. Happy days!

ARTICLE: Linking SQL Server databases together via linked servers

This entry describes the process of linking two remote SQL Server databases via a remote SQL Server client. The process is known in SQL Server as Linked Servers. Once databases are linked, tables in the source database server can be accessed on the destination database server.

Assumptions
  • The first remote server shall be referred to as source and second remote server shall be referred to as destination. Once the database linking has been completed, the source database tables can be accessed when connected to the destination database.
  • That the source, destination and client database software are installed correctly and in working order.
  • Readers have a basic understanding of SQL and the dialects used by the above vendor.
  • SQL Server command usages coloured blue, examples coloured green.

Environment
  • Source and destination server version / platform: 64-bit SQL Server 2008 Developer Edition on 64-bit Windows Server 2008 R2. 
  • Client version / platform: SQL Server 2008 Client on 64-bit Windows 7 Professional.
  • Settings for SQL Server are added / modified via the console in SQL Server 2008 Management Studio.
  • The following is to be used in a development environment with no thought give to database security between servers - this is a factor which bears consideration in all production environments and even some development environments.

SQL Server - Linked Servers
  1. Log into the destination server via the SQL Server 2008 Management Studio with user of sufficient privileges.
  2. In the console, execute the following to create the linked server.
    Usage:

        EXEC sp_addlinkedserver
            @server='
    [unique_name_of_linked_server]',
            @datasrc='[
    linked_server_hostname]',
            @srvproduct='',
            @provider='SQLNCLI'

    Example:
        EXEC sp_addlinkedserver
            @server='SRCDBLINK',
            @datasrc='SRCDBSRV',
            @srvproduct='',
            @provider='SQLNCLI'
  3. In the console, execute the following to create the login for the above linked server.
    Usage:
        EXEC sp_addlinkedsrvlogin
            @rmtsrvname='[unique_name_of_linked_server]',
            @useself='false',
            @locallogin='[domain]\[username]',
            @rmtuser='[username]',
            @rmtpassword='[password]'
    Example:
        EXEC sp_addlinkedsrvlogin
            @rmtsrvname='
    SRCDBLINK',
            @useself='false',
            @locallogin='CORP\sqlsuser',
            @rmtuser='
    sqlsuser',
            @rmtpassword='
    sqlsuser1'
  4. In the console, execute the following to update for RPCs (remote procedure calls) rpc and rpc out for the link.
    Usage:
         EXEC sp_serveroption '[unique_name_of_linked_server]', 'rpc', 'true';
    Example:
        EXEC sp_serveroption '
    SRCDBLINK', 'rpc', 'true';
    Usage:
         EXEC sp_serveroption '[unique_name_of_linked_server]', 'rpc out', 'true';
    Example:
       
    EXEC sp_serveroption 'SRCDBLINK', 'rpc out', 'true';
  5. Finally, to access the source database table via a connection to destination database, use the client to connect to the destination database execute the following.
    Usage:
       
    SELECT * FROM [[database_link]].[[database_name]].[dbo].[[table]].
    Example:

        SELECT * FROM [
    SRCDBLINK].[MyDatabase].[dbo].[MyTable]

ARTICLE: Linking Oracle databases together via database links

This entry describes the process of linking two remote Oracle databases via a remote Oracle client. The process is known in Oracle as Database Links. Once databases are linked, tables in the source database server can be accessed on the destination database server.

Assumptions
  • The first remote server shall be referred to as source and second remote server shall be referred to as destination. Once the database linking has been completed, the source database tables can be accessed when connected to the destination database.
  • That the source, destination and client database software are installed correctly and in working order.
  • Readers have a basic understanding of SQL and the dialects used by the above vendor.
  • Oracle command usages coloured blue, examples coloured green.

Environment
  • Source and destination server version / platform: 64-bit Oracle Enterprise Server 11g r2 on 64-bit Red Hat Enterprise Linux v5 r2. 
  • Client version / platform: 64-bit Oracle Client 11g r2 on 64-bit Windows 7 Professional.
  • Settings for Oracle are added / modified via the use of a command line client (SQLPLUS) connected only to the destination server. The tnsnames.ora should be modified using your favourite text editor.
  • The following is to be used in a development environment with no thought give to database security between servers - this is a factor which bears consideration in all production environments and even some development environments.

Oracle - Database Links
  1. The clients tnsnames.ora file must be updated with entries for both the source and destination databases.
  2. In order to create the (uni-directional) database link from the Oracle destination database to the Oracle source database, the destination database must have an entry in it's tnsnames.ora file for the source database. If the destination database does not said entry, it will need to have its said details supplied during the creation of the database link - ensure this entry exists.
  3. On the source database, create the source user used in the database link. Usage: CREATE USER [username] IDENTIFIED BY [password];. Example: CREATE USER srcUser IDENTIFIED BY srcUser;
  4. On the source database, grant permissions to the source user. Usage: GRANT CONNECT, RESOURCE, DBA TO [username];. Example: GRANT CONNECT, RESOURCE, DBA TO srcUser;.
  5. On the destination database, create the destination user who will be logged into SQLPLUS to create the database link and who will also have read/write access to the destination database. Usage: CREATE USER [username] IDENTIFIED BY [password];. Example: CREATE USER destUser IDENTIFIED BY destUser; 
  6. On the destination database, grant permissions to the destination user. Usage: GRANT CONNECT, RESOURCE, DBA TO [username];. Example: GRANT CONNECT, RESOURCE, DBA TO destUser;.
  7. On the destination database, logged in to the destination database as the above destUser, create the uni-directional database link. Usage: CREATE DATABASE LINK [source_database_link_name] CONNECT TO srcUser IDENTIFIED BY srcUser USING '[oracle_sid|tnsname_connection_details]';. Example: CREATE DATABASE LINK sourceDBLink CONNECT TO srcUser IDENTIFIED BY srcUser USING 'ORA11GU'; 
  8. Finally, to access the source database table via a connection to destination database, use the client to connect to the destination database execute the following. Usage: SELECT * FROM [user].[table_name]@[src_database_link];. Example: SELECT * FROM MyUser.MyTable@sourceDBLink;

ARTICLE: Linking DB2 databases together via federation

This article describes the process of linking two remote DB2 databases via a remote DB2 client. The process is known in DB2 as Database Federation. Once linked, tables in the source database server can be accessed on the destination database server. I wrote this as I found a dearth of information online regarding this as well as the IBM Inforcentre to be confusing.

Assumptions
  • The first remote server shall be referred to as source and second remote server shall be referred to as destination. Once the linking / federation has been completed, the source database tables can be accessed when connected to the destination database.
  • That the source, destination and client database software are installed correctly and in working order.
  • Readers have a basic understanding of SQL and the dialects used by the above vendor.
  • DB2 command usages coloured blue, examples coloured green.
  • Note that as of DB2 9.7 FP2, the process of linking DB2 databases together via federation is at no additional cost to the servers purchased - you do not need to purchase IBM Infosphere to link two DB2 database together.

    Environment
    • Source and destination server version / platform: 64-bit DB2 Enterprise Server Edition v9.7 FP2 on 64-bit Red Hat Enterprise Linux v5 r2. 
    • Client version / platform: 64-bit DB2 Data Server Client v9.7 FP2 on 64-bit Windows 7 Professional.
    • Settings for DB2 are added / modified via the use of a command line client (DB2CMD) initialised for DB2, connected only to the destination server. 
    • The following is to be used in a development environment with no thought give to database security between servers - this is a factor which bears consideration in all production environments and even some development environments.
    • In the scenario below, the source server resides on DB2SRCSRV and the destination server resides on DB2DESTSRV. Both run on port's 50000. The DB2 user which is used below on both servers is db2inst1 with password db2inst1.

    DB2 - Federation
    1. Via the client, catalogue the destination server node. Usage: CATALOG TCPIP NODE [dest_node_name] REMOTE [dest_hostname|dest_ip] SERVER [dest_port] WITH "[short_desc]". Example: CATALOG TCPIP NODE DB2V97D REMOTE DB2VDESTSRV SERVER 50000 WITH "SRC NODE"
    2. Via the client, attach to the destination server node. Usage: ATTACH TO [dest_node_name] USER [dest_username] USING [dest_password]. Example: ATTACH TO DB2V97D USER db2inst1 USING db2inst1
    3. Ensure that the DB2 Database Manager's FEDERATED variable is set to YES on just the destination server. This should not be done for the source server.  Usage: UPDATE DBM CFG USING FEDERATED [YES|NO]. Example: UPDATE DBM CFG USING FEDERATED YES. Note that once completed, the database manager and database instance may need to be restarted.
    4. The source server node needs to be catalogued on the destination server. Usage: CATALOG TCPIP NODE [src_node_name] REMOTE [src_hostname|src_ip] SERVER [src_port] WITH "[short_desc]". Example: CATALOG TCPIP NODE DB2V97S REMOTE DB2SRCSRV SERVER 50000 WITH "SRC NODE"
    5. The source database needs to be catalogued (at the above src DB2V97S node) on the destination server. Usage: CATALOG DATABASE [src_db] AT NODE [src_node_name] AUTHENTICATION SERVER WITH "[short_desc]". Example: CATALOG DATABASE SRCDB AT NODE DB2V97S AUTHENTICATION SERVER WITH "SRC DB"
    6. Via the client, connect to the destination database. Usage CONNECT TO [dest_db] USER [dest_user] USING [dest_password]. Example: CONNECT TO DESTDB USER db2inst1 USING db2inst1
    7. Create the DRDA wrapper to allow the destintation database to connect to the source database. Usage / example: CREATE WRAPPER DRDA LIBRARY 'libdb2drda.so';
    8.  Create the server definition which will manage the remote connection to the source database. Usage: CREATE SERVER [src_db] TYPE DB2/UDB VERSION '9.7' WRAPPER DRDA AUTHID "[dest_db_user]" PASSWORD "[dest_db_password]" OPTIONS (ADD DBNAME '[src_db]');. Example:
      CREATE SERVER SRCDB TYPE DB2/UDB VERSION '9.7' WRAPPER DRDA AUTHID "db2inst1" PASSWORD "db2inst1" OPTIONS(ADD DBNAME 'SRCDB');
    9. Create the user mapping for the source database to allow the destination database to connect. Usage: CREATE USER MAPPING FOR [dest_db_user] SERVER [src_db] OPTIONS (ADD REMOTE_AUTHID '[src_db_user]', ADD REMOTE_PASSWORD '[src_db_password]');. Example: CREATE USER MAPPING FOR DB2INST1 SERVER SRCDB OPTIONS (ADD REMOTE_AUTHID 'db2inst1', ADD REMOTE_PASSWORD 'db2inst1');
    10. Unlike Oracle or SQL Server where remote database tables can be accessed directly from a connection to the local database with SQL such as SELECT * FROM [user].[table]@[src_database_link] for Oracle and SELECT * FROM [src_linked_server].[database].[dbo].[table] for SQL Server, DB2 requires an additional step before these remote databases can be accessed: to create nicknames for each of the tables in the remote database as it has no sense of directly referencing the link in the SQL itself. The nickname of the remote table must be unique to to all existing tables within the destination database. This nickname allows a user with sufficient privileges to access a table on the source database while being connected to the destination database. Note that the ZZZ prefix ensures that the source database table nickname will remain unique in the destination database. Usage: CREATE OR REPLACE NICKNAME [dest_db_user].[remote_table_name] FOR [src_db].[src_table]. Example: CREATE OR REPLACE NICKNAME db2inst1.ZZZMyTable FOR SECSRC.db2inst1.MyTable;.
    11. Finally, to access the source database table via it's new destination database nickname, use the client to connect to the destination database execute the following. Usage: SELECT * FROM [src_table_nickname];. Example: SELECT * FROM ZZZMyTable;