/opt/ibm/WebSphere/AppServer/profiles/AppSrv06/bin/wsadmin.sh -f deploy.py -lang jython -user xxx@cn.ibm.com -password qaz1wsx2

Create a RTC build engine : "RationalBuildForgeConnector" http://jazz.net/forums/viewtopic.php?p=46073&sid=405b9ce85df26eb93a3bfc0569b1ffc7#46073 https://jazz.net/wiki/html/bin/view/Main/RationalBuildForge/IntegrationWithRTC#Setup_an_RTC_Run_As_user_for_ass The example is installed with the Build System Toolkit; it can be found at opt/ibm/TeamConcertBuild/buildsystem/buildtoolkit/examples/standalone. The build.xml file in that directory contains the steps to follow to run the example.

SCM tools cp /datad/eadftp/public/dsw_dev_tools/repositories/native/scmtools_0.7.0.I20091211-1048.zip . Use below command line to check out source code. scm login -r https://btsjazz.dyn.webahead.ibm.com:9445/jazz -u xxx@cn.ibm.com -P qaz1wsx2 scm load -d . -r https://btsjazz.dyn.webahead.ibm.com:9445/jazz "Build on SQO Head Workspace" modify scm.ini to set jre path. Template Getting Started with Project Areas and Process in Rational Team Concert 2.0

Setup Uninstall DB2 on ubuntu $DB2DIR/instance/db2idrop intancename dasdrop dasname userdel -r db2inst1 $DB2DIR/install/db2_deinstall -a Installation http://sivyer.javaeye.com/blog/149073

1. run sudo ./db2setup
2. choose will as the instance user

   db2_install

第四步：创建用户 /usr/sbin/groupadd db2iadm1 /usr/sbin/groupadd db2fadm1 /usr/sbin/useradd –m -g db2iadm1 –d /home/db2inst1 db2inst1 /usr/sbin/useradd –m -g db2fadm1 –d /home/db2fenc1 db2fenc1 ibm.Com

/usr/sbin/useradd -m -g db2iadm1 -d /home/db2inst1 db2inst1

/opt/ibm/db2/V9.1/instance/db2icrt -a server -u db2fenc1 db2inst1

su db2inst1

db2stop db2set DB2COMM=tcpip db2 update dbm cfg using SVCENAME db2c_jazz db2 update dbm cfg using INDEXREC ACCESS db2start db2 create database JAZZ using codeset UTF-8 territory en PAGESIZE 8192 db2 get dbm cfg su cd /opt/IBM/WebSphere/AppServer/JazzTeamServer/server ./repotools.sh -createTables

Install from RTC setup files.

.file=C153CML ..title=DB2 Workgroup Server Edition Authorized User Option - Activation ..size=476932 ..sha=AC5D6E6C41A07DA2B24146C8A11270243DF01544 ..stat=1 ..name=DB2_WSE_Auth_User_Activation_V95.zip ..path=/home/jazz/DownloadDirector ..unpack=1 ..crc= ..date=1192553100 .allFiles=false .srvURL=http://www15.software.ibm.com/tqd

cat /proc/meminfo

[jazz@dswdev3 ~]$ ipcs -l

------ Shared Memory Limits -------- max number of segments = 4096 max seg size (kbytes) = 262144 max total shared memory (kbytes) = 201326592 min seg size (bytes) = 1

------ Semaphore Limits -------- max number of arrays = 1024 max semaphores per array = 250 max semaphores system wide = 32000 max ops per semop call = 32 semaphore max value = 32767

------ Messages: Limits -------- max queues system wide = 1024 max size of message (bytes) = 65535 default max size of queue (bytes) = 65536

一、 DB2卸载 1． 以db2admin进入终端，执行./db2stop，停止DB2服务。 2． 进入/usr/opt/db2_08_01/instance，卸载现有DB2示例。 3． 进入DB2安装程序目录，执行db2_deinstall，卸载DB2。 4． 输入ps –ef | grep db2 查看内存中是否还有db2相关程序运行，若有则输入 kill –9 parentId 删除掉db2残留程序。 5． 删除db2admin、db2fenc用户。 6． 进入/usr/opt，删除db2_08_01文件夹。 7． 进入/home中，删除db2admin、db2fenc两文件夹。 8． 进入/var中，删除文件夹db2.

二、 WebSphere卸载

1. 进入/usr/WebSphere/AppServer/_uninst，执行uninstall，卸载WebSphere。
2. 进入/usr中，删除WebSphere、IBMHttpServer两个文件夹。

will@will IBM$ hostname
will
will@will IBM$ sudo gedit /etc/hosts

Users and Groups —> Administrative user roles uid=917904672,c=cn,ou=bluepages,o=ibm.com you can find the uid in the blue page when click on "Same manager" link RTC .file=CZ3TAML ..title=IBM Rational Team Concert Enterprise Edition - Server and Optio ..size=1709225773 ..sha=415108103D94BA0B957C9F9873ECB8446BBBE3FE ..stat=1 ..name=RTC-Enterprise-Full-2.0-Lin32-Local.zip ..unpack=1 ..crc= ..path=/home/jazz/DownloadDirector

Download from IBM Software Downloads

internet1.lotus.com https://localhost:9443/jazz/setup AMDIN/ADMIN Create blue group quotejazzadmin and quotejazzuser on https://bluepages.ibm.com/tools/groups/protect/groupsgui.wss LDAP Registry Location: ldap://bluepages.ibm.com:389 Base User DN: c=cn,ou=bluepages,o=ibm.com User Property Names Mapping:userId=mail,name=callupname,emailAddress=mail Base Group DN:ou=memberlist,ou=ibmgroups,o=ibm.com Jazz to LDAP Group Mapping: JazzAdmins=quotejazzadmin,JazzUsers=quotejazzuser,JazzDWAdmins=quotejazzadmin,JazzGuests=JazzGuests Group Name Property: cn Group Member Property: uniqueMember Bluepage LDAP http://reswat2.research.ibm.com/users/walicki/walickihomepage.nsf/html/bluepages.html http://v25was101.mkm.can.ibm.com/directory/bluegroups/gmschema.shtml http://techlab.rtp.raleigh.ibm.com/ibm/bluepages/Search-the-BluePages-LDAP-directory.htm 使用 Rational Team Concert 构建团队 JazzAdmins: Repository 的管理员，对于 Repository 具有完全的读写权限，可以对 Repository 内的数据进行任何操作。 JazzDWAdmins: 可以控制 Jazz 服务器端数据的 Repository 管理员。 JazzGuests: 对于 Repository 只有读权限的 Jazz 用户。 JazzUsers: 对于 Repository 具有常规读写权限的用户，如可以更改项目域，流程模板，但不能创建，可以创建或修改团队域，构建定义等等。 Reporstory http://apollobuildserv4.ottawa.ibm.com:8080/integration/2.0/install-repository/standard/ Cron job

yum install vixie-cron crontabs

[root@dswdev3 jazz]# cat /etc/crontab SHELL=/bin/bash PATH=/sbin:/bin:/usr/sbin:/usr/bin MAILTO=root HOME=/

00 16 * * * root run-parts /etc/cron.myreport 01 * * * * root run-parts /etc/cron.hourly 02 4 * * * root run-parts /etc/cron.daily 22 4 * * 0 root run-parts /etc/cron.weekly 22 4 * * 0,4,6 root run-parts /etc/cron.jazz 42 4 1 * * root run-parts /etc/cron.monthly

[root@dswdev3 inbox]# /etc/init.d/crond reload

Jazz SCM FAQ: import from SVN or CVS Importing data from Subversion and CVS into Jazz SCM An instruction to import source code. 3.75G in cvs server jbe -repository https://localhost:9445/jazz -engineId dsw -userId xxx@cn.ibm.com -pass password testjazz.txt Websphere Integration Developer Change WAS ports Creating profiles WID7 and WAS7 WebSphere 7 migration notes Eclipse p2 reconciler returned with error status=13 applying updates to Rational Developer for System i Download Setup Files http://w3-103.ibm.com/software/xl/download/ticket.do Part Num: CR9NEML,CR9PIML Profile on ubuntu create profile in /home/will/quote/server7/SQO_Head without security.

will@will-laptop:/opt/ibm2/WID7_WTE/runtimes/bi_v7/bin/ProfileManagement$ ./pmt.sh

http://shjy-nicholas.javaeye.com/blog/413483 vi /etc/security/limits.conf 加上：

• soft nofile 65535
• hard nofile 65536

执行ulimit -a 默认为 open files (-n) 1024 vi ~/.bashrc 加上 ulimit -n 65536 保存，重启，即可。

China WAS Question DB => Notes://CSDLD01/482570960036D876/

WAS Central DB => Notes://D27DBL06/862569E70075B687/

WAS 6.1 infocenter => http://publib.boulder.ibm.com/infocenter/wasinfo/v6r1/index.jsp

WebSphere Application Server detailed system requirements => http://www-01.ibm.com/support/docview.wss?rs=180&uid=swg27006921

Redbooks WebSphere domian => http://www.redbooks.ibm.com/portals/websphere Publish Error on RAD 7.5 + WAS 7.0 Sometimes if you stop mannually the publish process for you application at the middle, and will meet the following exception while republishing your application: [19/06/09 10:53:42:046 BST] 00000036 SystemErr R com.ibm.websphere.management.exception.AdminException: A composition unit with name Edu_________t already exists. Select a different application name. I guess the mannual temination of the publish process will conrupt the configuration file at WAS, and therefore the past publish state will remain zombie files and make the later publish process fail. If you logon to the WAS 7.0 admin console, click on Business-Level Application and you will see its status is unknown. Further if you click on the composition unit Edu________t you will also see its status is unknow. On the console, you cannot delete the composition unit since it is corrupted.

Below would be one of the solution. I try delete the composition unit (cus) in my local directory (see below screenshot) and then restart the server. Then the application will work well again. Beside, there are some temp files on the WAS server, you can also delete them if the about mentiond cannot fix your problem. C:\Program Files\IBM\WebSphere\AppServer\profiles\AppSrv01\config\cells\MyComputer-Node01Cell\blas\Edu_____________t\ C:\Program Files\IBM\WebSphere\AppServer\profiles\AppSrv01\temp\*.* C:\Program Files\IBM\WebSphere\AppServer\profiles\AppSrv01\wstemp\*.* Good luck find . -iname quoteEAR -exec rm -rf {} \; IBM WebSphere Application Server V7 Administration on Windows You can download the installables from FTP server before the training, the FTP server is 9.181.87.58, pass the BSO first. Log on with anonymous user. You will see three directories. 150 Here comes the directory listing. drwxr-xr-x 3 0 0 4096 Jul 21 05:46 NewTester drwxr-xr-x 2 0 0 4096 Nov 20 04:59 VMWareCourse drwxr-xr-x 7 0 0 4096 Sep 25 02:58 WAS7Training The VMWareCourse dir contains all virtual images for this course. See the pdf instruction in it. And WAS7Training contains the separate drivers, we will introduce it in the class. Hot deployment and dynamic reloading http://publib.boulder.ibm.com/infocenter/wasinfo/v6r0/index.jsp?topic=/com.ibm.websphere.nd.doc/info/ae/ae/trun_app_hotupgrade.html http://publib.boulder.ibm.com/infocenter/wasinfo/v6r0/index.jsp?topic=/com.ibm.websphere.nd.doc/info/ae/ae/trun_app_hotupgrade.html http://publib.boulder.ibm.com/infocenter/wasinfo/v7r0/index.jsp?topic=/com.ibm.websphere.nd.doc/info/ae/ae/trun_app_hotupgrade.html

C1Q24ML, C1Q25ML, C1Q26ML, C1Q28ML Installing WebSphere Application Server 6.1 on Ubuntu Create profile

sudo unlink /bin/sh sudo ln -s /bin/bash /bin/sh cd /opt/ibm/WebSphere/ID/602/runtimes/bi_v6/bin/ProfileCreator sudo ./pctLinux.bin Setup db2 connection . db2profile /opt/ibm/WebSphere/ID/602/wid.bin & Target of the document The document should help you to install WebSphere Integration Developer on Linux. The root cause for creation of the document was the fact that the official documentation in the WebSphere InfoCenter is a bit weak regarding details on that topic.

Environment For the setup I used the following environment: Operating System: SUSE SLES 10 SP1 with graphical user interface Users: root, widuser:users WebSphere Integration Developer: Version 6.1.2 WID Fixpack: 6.1.2 InterimFix 001 I used a VMWare image which increased during installation up to 20 GB.

Required software packages: Mozilla Firefox WebSphere Integration Developer 6.1.2 Packages (C1H58ML) WebSphere Integration Developer v6.1.2 Multiplatform Multilingual 1 of 4

(C1H59ML) WebSphere Integration Developer v6.1.2 Multiplatform Multilingual 2 of 4

(C1H5AML) WebSphere Integration Developer v6.1.2 Multiplatform Multilingual 3 of 4

(C1H5BML) WebSphere Integration Developer v6.1.2 Multiplatform Multilingual 4 of 4

WID Fixpack wid612_interim_fix001.zip

Preparation of the environment You need to have root access to install WID (see Related Links: WID Installation User requirements) Make sure that ulimit is set to 2048 or higher (see Related Links: WID ulimit setup) If you want to view the release notes and the installation guide you need a supported web browser. (see Related Links: WID Browser Requirements)

Installation

1.Preparation 1.Log on as root 2.Check the ulimit -n

2.Extract the software packages 1.Create a temporary installation directory (for this is /var/tmp/wid) 2.Unzip the packages C1H58ML, C1H59ML, C1H5AML and C1H5BML to the temporary directory. The right order seems to be not important.

3.Create the responsefile to install the IBM Installation Manager and WID silently We use here the existing one but you can also create your own one (see comments) 1.Switch to the directory /var/tmp/wid/disk1/IM_linux

2.Copy the existing response file ../util/responsefile_linux to the current directory 3.Modify the responsefile 1.By default you do not have to modify the responsefile if you do not want to install additional languages. Comment: Due to a bug in the responsefile you have to edit the responsefile. In the section <offering profile='IBM WebSphere Integration Developer' version='6.1.2000.20080614_1858'

features='com.ibm.wid.wps,com.ibm.wid.wps.wpsprofile,com.ibm.wid.wps.esbprofile' id='com.ibm.wid.wps'/>

you must change the version of the WID profile version='6.1.2000.20080618_0153'

1. You can remove the additional languages for installation manager to just have de and en installed <data key='eclipseLocation' value='/opt/IBM/InstallationManager/eclipse'/>

   <data key='cic.selector.nl' value='en,de'/>

3.You can add de as additional languages for WID

<data key='eclipseLocation' value='/opt/IBM/WID61'/>

<data key='cic.selector.nl' value='en,de'/>

4.Install the product 1.From the current directory (IM_linux) run the command

./install --launcher.ini silent-install.ini -input responsefile_linux.txt

2.It will take up to 6 hours to install the product

5.Check the logs if the installation was successful 1.The logs are located at /var/ibm/InstallationManager/logs

2.The main log file has the format YYYYMMDD_HHMM.xml

3.There are additional log files in subdirectories which the main log file refers to. 4.The last section of the log file should contain content like <entry num='145' time='1220326951240' elapsed='05:37:54.83' level='INFO' thread='main'>

<message>OK</message>

</entry>

Comments:

You can create your own responsefile for WID install by using the IBM Install Manager record feature . Run the command ./install -record home/temp/wid612responsefile.xml -skipInstall /home/temp/wid/temp

This creates the response file /home/temp/wid612responsefile.xml.

In addition the directory /opt/IBM/InstallationManager is created but without content. You can remove that directory.

After the installation you can remove the temporary files under /var/tmp/wid.

Update the WID installation

We want to apply the wid fix silently. So we have to create another responsefile to do so. Unfortunately there is no responsefile available that could be modified (I did not find one). So I created one which I used. Under comments you find details how to create one.

1.Extract the fixpack to the temporary directory /var/tmp/wid via command unzip wid612_interim_fix001.zip

2.Switch to the directory /opt/IBM/InstallationManager/eclipse

3.Copy the responsefile IBMIM_responsefile.txt to the eclipse directory

4.Edit the responsefile 1.Change the path for the repository to point to the directory where you extracted the fixpack. The path must point to a directory with the file repository.config. 2.If you want to install a different fixpack modify the line <offering profile='IBM WebSphere Integration Developer' version='6.1.2.000_20080805_1422' features='-' id='com.ibm.websphere.integration.developer'/> The related version number can be found by extracting the fixpack and looking at repository.xml

5.Run the installer 1.In the eclipe directory execure as root the command ./IBMIM --launcher.ini silent-install.ini -input IBMIM_responsefile.txt

2.The installation takes around 10 minutes.

6.Check the logs if the installation was successful 1.The logs are located at /var/ibm/InstallationManager/logs

2.The log file has the format YYYYMMDD_HHMM.xml

3.You should see at the end a section like <entry num='102' time='1220338224068' elapsed='08:37.22' level='INFO' thread='main'>

...

<message>OK</message>

</entry>

Comments:

You can create your own responsefile for WID install by using the IBM Install Manager record feature . Unfortunately you cannot create the file without fixpack installation. Run the command ./IBMIM -record /var/tmp/IBMIM_responsefile.txt

This creates the response file /var/tmp/IBMIM_responsefile.txt and installs the fix. If you try to create the responsefile by using the option -skipInstall the existing installation of WID is not displayed in the first panel. So you cannot proceed there.

After the installation you can remove the temporary files under /var/tmp/wid.Comment:

Check the installation

You can start WID via icon but also via command line. 1.Command to start wid as it would be done using the launcher:

/opt/IBM/WID61/eclipse -product com.ibm.wbit.feature.ide -showlocation

2.Easier command to start WID:

/opt/IBM/WID61 # ./wid.bin

3.Default Workspace: /root/IBM/wid6.1/workspace (This should be changed to something like /var/wid/myworkspace)

2.Check the version

1.Click on Help->About WebSphere Integration Developer 6.1.2

2.You should see something similar to Version: 6.1.2, Build id: 6.1.2ifix001-20080805_0917

3.Check if both server configurations are available

1.In the business integration perspective you should have a server view containing the servers for ESB and WPS.

2.If you just see the WPS server ... 1.This does not mean that the other server has not been defined.

2.Close the view and reopen it via Window->Show View->Servers.

This should display the ESB server as well.

4.Check if the test environment could be started 1.Start the servers (one at the time) 2.If you get a pop-up window telling "Canceling Server Start". .. 1.Click on OK and you should get a message indicating a Timeout after 600s.

2.Check if the WID server status displays the server as successfully started or not. If this is the case this indicates a timeout issue.

3.Check the logs at /opt/IBM/WID61/pf/wps/logs/server1 or /opt/IBM/WID61/pf/esb/logs/server1 for errors

4.If there is no issue change the timeout value for the server startup via

Window->Preferences->Server->Server timeout delay (default is normal, change to long)

3.Try to access the admin console via browser

1.WPS: URL: http://localhost:9060/ibm/console User: admin, password: admin

2.WESB: URL: http://localhost:9061/ibm/console User: admin, password: admin

Switch to non-root

1.To allow a non-root user to run WID including WAS you have to change the file access to allow the non-root user to create and modify files.

1.Log on as root

2.Change the user rights for the workspaces to provide to the restricted user write access . This can be done for example by the command: chmod -R 777 /var/wid

3.Change the user rights for the WID files to allow creation and modifications on WID and runtime . This can be done for example by the command: chmod -R 777 opt/IBM/WID61

4.You might be able to restrict this file access to some directories (for ex. pf for the profiles) but does it make sense?

2.Log in as non-root user and start WID from /opt/IBM/WID61 via command ./wid.bin

Related links: IBM Installation Manager Documentation http://publib.boulder.ibm.com/infocenter/dmndhelp/v6r1mx/topic/com.ibm.wbit.610.help.install.doc/pdf/install_book.pdf WID ulimit setup http://publib.boulder.ibm.com/infocenter/dmndhelp/v6r1mx/topic/com.ibm.wbit.612.help.install.doc/topics/t_increase_handles.html WID Silent Install http://publib.boulder.ibm.com/infocenter/dmndhelp/v6r1mx/topic/com.ibm.wbit.612.help.install.doc/topics/t_installing_silently_overview11.html WID Installation User requirements http://publib.boulder.ibm.com/infocenter/dmndhelp/v6r1mx/topic/com.ibm.wbit.612.help.install.doc/topics/r_user_priv.html WID Installation SW Requirements http://publib.boulder.ibm.com/infocenter/dmndhelp/v6r1mx/topic/com.ibm.wbit.612.help.install.doc/topics/r_insoftreq.html WID Browser Requirements: http://publib.boulder.ibm.com/infocenter/dmndhelp/v6r1mx/topic/com.ibm.wbit.612.help.install.doc/topics/t_inothersw.html WID Supported Operating Systems http://publib.boulder.ibm.com/infocenter/dmndhelp/v6r1mx/topic/com.ibm.wbit.612.help.install.doc/topics/r_inosreq.html

IBM Packaging Utility http://www-01.ibm.com/support/docview.wss?uid=swg24024683#install Repo ftp://ead:3edc4rfv@btsjazz.dyn.webahead.ibm.com/public/dsw_dev_tools/rep/ ftp://ead:3edc4rfv@btsjazz.dyn.webahead.ibm.com/public/dsw_dev_tools/repositories/ https://www.ibm.com/software/rational/repositorymanager/repositories/rationalsdp7 datad/eadftp/public/dsw_dev_tools/RTC_Client2.0.0.2/RTC-Eclipse-Clnt-2.0.0.2-Win32-Local/im-repo/repository.config http://public.dhe.ibm.com/software/websphere/repositories http://btsjazz.dyn.webahead.ibm.com:8000/ datad/eadftp/public/dsw_dev_tools/rep datad/eadftp/public/dsw_dev_tools/repositories Setup SQO WAS 7 evaluation tracking document IBM Installer Manager repositories in our FTP server get a bug in WAS6.0 http://www-01.ibm.com/support/docview.wss?uid=swg1PM07608. WebSphere Process Server V7.0 for Windows X86-32/64bit Systems Multilingual eAssembly (CR9C2ML) WebSphere Process Server Version 7.0 Feature Pack Version 1.0 Multiplatform Multilingual eAssembly (CRBE5ML) Remote debug http://www.jacoozi.com/index.php?option=com_content&task=view&id=119&Itemid=134 http://www.eclipsezone.com/eclipse/forums/t53459.html https://www.ibm.com/developerworks/opensource/library/os-ecbug/ Debugging IBM WebSphere 5.x/6.x (Thanks to Michael Murphy) Open WebSphere 5.X/6.X Console Navigate to Servers Application Servers [SERVERNAME] Process Definition Java Virtual Machine Check off Debug Mode Arguments can be edited in the field called Debug arguments. You can paste this line and use the same port 1044 to debug from Eclipse: Xdebug -Xrun__my_company_1__wp:transport=dt_socket,server=y,suspend=n,address=1044 create profile WAS admin setup login admin console: Users and Groups —> Administrative user roles —> add http://w3.ibm.com/jct03019wt/bluepages/searchByName.wss?uid=AVALEL672&task=viewrecord http://w3.ibm.com/jct03019wt/bluepages/searchByName.wss?uid=AVAKGF672&task=viewrecord uid=AVALEL672,c=cn,ou=bluepages,o=ibm.com uid=AVAKGF672,c=cn,ou=bluepages,o=ibm.com Change pass word Update the defined db2 user and password in security file: File path: profiles\AppSrv01\config\cells\cnqinfengcNode01Cell\security.xml 1.Find this item: <authDataEntries xmi:id="JAASAuthData_1230536298391" alias="cnqinfengcNode01/for header prcbkwb1" userId="web_dev2" password="{xor}Oj02JW1vKSw="/> 2.Update the user_id to an not exists one, such as aaaa_aaa. Save the change 3.start your server and change the user_id and password in admin console com.ibm.ws.security.util.PasswordEncoder DB2 Skill 必需步骤： 您已经启用了 DB2 扩展 Windows 安全性。您必须将运行 DB2 本地应用程序或工具的 DB2 用户添加至 DB2ADMNS 或DB2USER 组 可以使用端口号 "50000" 连接至 DB2 实例 "DB2"。请记录此端口号以供将来参考。 DB2 XML SQL and XQuery tutorial series for IBM DB2

Java操作DB2 XML数据实践 XQuery samples These samples demonstrate the use of axes, FLWOR expressions, and queries written with XQuery and SQL/XML. These samples can be found in the following location: On Windows(R): %DB2PATH%\sqllib\samples\xml (where %DB2PATH% is a variable that determines where DB2(R) is installed) * On UNIX(R): $HOME/sqllib/samples/xml (where $HOME is the home directory of the instance owner) db2 -td~

CREATE DATABASE xmltut USING CODESET GBK TERRITORY US~

CREATE TABLE Customer (Cid BIGINT NOT NULL PRIMARY KEY, Info XML)~

CREATE UNIQUE INDEX cust_cid_xmlidx ON Customer(Info) GENERATE KEY USING XMLPATTERN 'declare default element namespace "http://posample.org"~ /customerinfo/@Cid' AS SQL DOUBLE~

INSERT INTO Customer (Cid, Info) VALUES (1000, '<customerinfo xmlns="http://posample.org" Cid="1000"> <name>Kathy Smith</name> <addr country="Canada"> <street>5 Rosewood</street> <city>Toronto</city> <prov-state>Ontario</prov-state> <pcode-zip>M6W 1E6</pcode-zip> </addr> <phone type="work">416-555-1358</phone> </customerinfo>')~

INSERT INTO Customer (Cid, Info) VALUES (1002, '<customerinfo xmlns="http://posample.org" Cid="1002"> <name>Jim Noodle</name> <addr country="Canada"> <street>25 EastCreek</street> <city>Markham</city> <prov-state>Ontario</prov-state> <pcode-zip>N9C 3T6</pcode-zip> </addr> <phone type="work">905-555-7258</phone> </customerinfo>')~

INSERT INTO Customer (Cid, Info) VALUES (1003, '<customerinfo xmlns="http://posample.org" Cid="1003"> <name>Robert Shoemaker</name> <addr country="Canada"> <street>1596 Baseline</street> <city>Aurora</city> <prov-state>Ontario</prov-state> <pcode-zip>N8X 7F8</pcode-zip> </addr> <phone type="work">905-555-2937</phone> </customerinfo>')~

SELECT * from Customer~

UPDATE customer SET info = '<customerinfo xmlns="http://posample.org" Cid="1002"> <name>Jim Noodle</name> <addr country="Canada"> <street>1150 Maple Drive</street> <city>Newtown</city> <prov-state>Ontario</prov-state> <pcode-zip>Z9Z 2P2</pcode-zip> </addr> <phone type="work">905-555-7258</phone> </customerinfo>' WHERE XMLEXISTS ( 'declare default element namespace "http://posample.org"; $doc/customerinfo[@Cid = 1002]' passing INFO as "doc")~

DELETE FROM Customer WHERE XMLEXISTS ( 'declare default element namespace "http://posample.org"; $doc/customerinfo[@Cid = 1003]' passing INFO as "doc")~

SELECT count(*) from Customer~

SELECT XMLQUERY ( 'declare default element namespace "http://posample.org"; for $d in $doc/customerinfo return <out>{$d/name}</out>' passing INFO as "doc") FROM Customer as c WHERE XMLEXISTS ('declare default element namespace "http://posample.org"; $i/customerinfo/addr[city="Toronto"]' passing c.INFO as "i")~

UPDATE COMMAND OPTIONS USING i ON~

-- 不使用SQL的情况下检索INFO列中所有的XML文档 XQUERY db2-fn:xmlcolumn ('CUSTOMER.INFO')~ -- 相当于 SELECT Info FROM Customer~

-- 全查询 XQUERY db2-fn:sqlquery ('SELECT Info FROM Customer')~

-- 检索和过滤XML值 XQUERY declare default element namespace "http://posample.org"; for $d in db2-fn:xmlcolumn('CUSTOMER.INFO')/customerinfo where $d/addr/city="Toronto" return <out>{$d/name}</out>~

XQUERY declare default element namespace "http://posample.org"; for $d in db2-fn:sqlquery( 'SELECT INFO FROM CUSTOMER WHERE Cid < 2000')/customerinfo where $d/addr/city="Toronto" return <out>{$d/name}</out>~

-- XML模式注册 REGISTER XMLSCHEMA 'http://posample.org' FROM 'file:///<C:/>customer.xsd' AS posample.customer COMPLETE~

REGISTER XMLSCHEMA http://posample.org/product.xsd FROM product.xsd \ AS myschema.product COMPLETE XMLSCHEMA myschema.product~

REGISTER XMLSCHEMA http://posample.org/product.xsd FROM product.xsd \ AS myschema.product COMPLETE~

customer.xsd <?xml version="1.0"?> <xs:schema targetNamespace="http://podemo.org" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"> <xs:element name="customerinfo"> <xs:complexType> <xs:sequence> <xs:element name="name" type="xs:string" minOccurs="1" /> <xs:element name="addr" minOccurs="1" maxOccurs="unbounded"> <xs:complexType> <xs:sequence> <xs:element name="street" type="xs:string" minOccurs="1" /> <xs:element name="city" type="xs:string" minOccurs="1" /> <xs:element name="prov-state" type="xs:string" minOccurs="1" /> <xs:element name="pcode-zip" type="xs:string" minOccurs="1" /> </xs:sequence> <xs:attribute name="country" type="xs:string" /> </xs:complexType> </xs:element> <xs:element name="phone" nillable="true" minOccurs="0" maxOccurs="unbounded"> <xs:complexType> <xs:simpleContent> <xs:extension base="xs:string"> <xs:attribute name="type" form="unqualified" type="xs:string" /> </xs:extension> </xs:simpleContent> </xs:complexType> </xs:element> <xs:element name="assistant" minOccurs="0" maxOccurs="unbounded"> <xs:complexType> <xs:sequence> <xs:element name="name" type="xs:string" minOccurs="0" /> <xs:element name="phone" nillable="true" minOccurs="0" maxOccurs="unbounded"> <xs:complexType> <xs:simpleContent > <xs:extension base="xs:string"> <xs:attribute name="type" type="xs:string" /> </xs:extension> </xs:simpleContent> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> <xs:attribute name="Cid" type="xs:string" /> </xs:complexType> </xs:element> </xs:schema>

INSERT INTO Customer(Cid, Info) VALUES (1003, XMLVALIDATE (XMLPARSE (DOCUMENT '<customerinfo xmlns="http://posample.org" Cid="1003"> <name>Robert Shoemaker</name> <addr country="Canada"> <street>1596 Baseline</street> <city>Aurora</city> <prov-state>Ontario</prov-state> <pcode-zip>N8X 7F8</pcode-zip> </addr> <phone type="work">905-555-7258</phone> <phone type="home">416-555-2937</phone> <phone type="cell">905-555-8743</phone> <phone type="cottage">613-555-3278</phone> </customerinfo>' PRESERVE WHITESPACE ) ACCORDING TO XMLSCHEMA ID posample.customer ))~

CREATE TABLE Customer (Cid BIGINT NOT NULL PRIMARY KEY, Info XML, History XML)

CREATE TABLE MyCustomer LIKE Customer; ALTER TABLE MyCustomer ADD COLUMN Preferences XML;

JDBC插入XML PreparedStatement insertStmt = null; String sqls = null; int cid = 1015; sqls = "INSERT INTO MyCustomer (Cid, Info) VALUES (?, ?)"; insertStmt = conn.prepareStatement(sqls); insertStmt.setInt(1, cid); File file = new File("c6.xml"); insertStmt.setBinaryStream(2, new FileInputStream(file), (int)file.length()); insertStmt.executeUpdate();

JDBC更新XML PreparedStatement updateStmt = null; String sqls = null; int cid = 1004; sqls = "UPDATE MyCustomer SET Info=? WHERE Cid=?"; updateStmt = conn.prepareStatement(sqls); updateStmt.setInt(1, cid); File file = new File("c7.xml"); updateStmt.setBinaryStream(2, new FileInputStream(file), (int)file.length()); updateStmt.executeUpdate();

-- 删除数据 DELETE FROM MyCustomer WHERE XMLEXISTS ('declare default element namespace "http://posample.org"; $d//addr[city="Markham"]' passing INFO as "d")~

-- XML列创建触发器 CREATE TRIGGER UPDAFTR AFTER UPDATE OF Info ON MyCustomer REFERENCING NEW AS N FOR EACH ROW BEGIN ATOMIC INSERT INTO CustLog VALUES(N.CID, CURRENT TIMESTAMP, 'Update'); END~

CREATE TRIGGER INSAFTR AFTER INSERT ON Customer REFERENCING NEW AS N FOR EACH ROW BEGIN ATOMIC INSERT INTO CustLog VALUES(N.CID, CURRENT TIMESTAMP, 'Insert'); END~

-- XML解析 INSERT INTO MyCustomer (Cid, Info) VALUES (?, xmlparse(document cast(? as clob(1k)) preserve whitespace))~

-- 在XQuery中，查询可以调用下列函数之一来获取DB2数据库中的输入XML数据：db2-fn:sqlquery 和 db2-fn:xmlcolumn。db2-fn:xmlcolumn函数将检索整个XML列，而db2-fn:sqlquery将检索基于SQL查询的 XML值。

db2-fn:xmlcolumn('BUSINESS.ORDERS.PURCHASE_ORDER')/shipping_address/city

db2-fn:sqlquery(" SELECT purchase_order FROM business.orders WHERE ship_date = '2005-06-15' ")/shipping_address/city

SQL是一种不区分大小写的语言 XQuery是一种区分大小写的语言

函数 XMLQUERY XMLTABLE 谓词 XMLEXISTS

SELECT XMLQUERY ('declare default element namespace "http://posample.org"; $d/customerinfo/phone' passing INFO as "d") FROM CUSTOMER~

VALUES (XMLQUERY ('declare default element namespace "http://posample.org"; db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo/phone'))~

SELECT Cid, XMLQUERY ('declare default element namespace "http://posample.org"; $d//addr[city="Aurora"]' passing INFO as "d") AS ADDRESS FROM CUSTOMER~

SELECT Cid, XMLQUERY ('declare default element namespace "http://posample.org"; $d/customerinfo/addr' passing c.INFO as "d") FROM Customer as c WHERE XMLEXISTS ('declare default element namespace "http://posample.org"; $d//addr[city="Aurora"]' passing c.INFO as "d")~

SELECT R.Pid FROM PURCHASEORDER P, PRODUCT R WHERE R.NAME = XMLCAST( XMLQUERY ('declare default element namespace "http://posample.org"; $d/PurchaseOrder/itemlist/item/product/name' PASSING P.PORDER AS "d") AS VARCHAR(128))~

SELECT Pid FROM PRODUCT ORDER BY XMLCAST(XMLQUERY ('declare default element namespace "http://posample.org"; $d/product/description/name' PASSING DESCRIPTION AS "d") AS VARCHAR(128))~

SELECT X.* FROM XMLTABLE (xmlnamespaces (DEFAULT "http://posample.org"), 'db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo' COLUMNS "CUSTNAME" CHAR(30) PATH 'name', "PHONENUM" XML PATH 'phone') as X~

SELECT X.* FROM XMLTABLE (xmlnamespaces (DEFAULT "http://posample.org"), 'db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo' COLUMNS "CUSTNAME" CHAR(30) PATH 'name', "PHONENUM" XML PATH 'phone') as X ORDER BY X.CUSTNAME~

INSERT INTO CUSTPHONE SELECT X.* FROM XMLTABLE (XMLNAMESPACES (DEFAULT 'http://posample.org'), 'db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo' COLUMNS "CUSTNAME" CHAR(30) PATH 'name', "PHONENUM" XML PATH 'document{<allphones>{phone}</allphones>}' )as X~

SELECT X.* FROM CUSTOMER C, XMLTABLE (xmlnamespaces (DEFAULT 'http://posample.org'), '$cust/customerinfo/phone' PASSING C.INFO as "cust" COLUMNS "CUSTNAME" CHAR(30) PATH '../name', "PHONETYPE" CHAR(30) PATH '@type', "PHONENUM" CHAR(15) PATH '.' )as X~

SELECT X.* FROM CUSTOMER C, XMLTABLE (xmlnamespaces (DEFAULT 'http://posample.org'), '$cust/customerinfo/phone' PASSING C.INFO as "cust" COLUMNS "CUSTNAME" CHAR(30) PATH '../name', "PHONETYPE" CHAR(30) PATH '@type', "PHONENUM" XML PATH '.' ) as X~

SELECT Cid FROM CUSTOMER WHERE XMLEXISTS ('declare default element namespace "http://posample.org"; $d//addr[city="Toronto"]' passing INFO as "d")~

SELECT * FROM CUSTOMER WHERE XMLEXISTS ('declare default element namespace "http://posample.org"; $d/customerinfo[@Cid=1000]' passing INFO as "d")~

SELECT * FROM CUSTOMER WHERE XMLEXISTS ('declare default element namespace "http://posample.org"; $d/customerinfo/@Cid=1000' passing INFO as "d")~

CREATE TABLE mytable (id BIGINT, xmlcol XML)~

CREATE INDEX myidx ON mytable(xmlcol) GENERATE KEY USING XMLPATTERN '//text()' AS SQL VARCHAR(255)~

SELECT xmlcol FROM mytable WHERE XMLEXISTS('$doc/CUSTOMER/ORDERS/ORDERKEY/text()="A512" ' PASSING xmlcol AS "doc")~

SELECT xmlcol FROM mytable WHERE XMLEXISTS('$doc/CUSTOMER[ORDERS/ORDERKEY/text()="A512"] ' PASSING xmlcol AS "doc")~

-- XML数据类型转换 SELECT XMLQUERY ('declare default element namespace "http://posample.org"; $d/customerinfo/addr' passing c.INFO as "d") FROM Customer as c WHERE XMLEXISTS('declare default element namespace "http://posample.org"; $d//addr[city=$cityName]' passing c.INFO as "d", 'Aurora' AS "cityName")~

SELECT XMLQUERY ('declare default element namespace "http://posample.org"; $d/customerinfo/addr' passing c.INFO as "d") FROM Customer as c WHERE XMLEXISTS('declare default element namespace "http://posample.org"; $d//addr[city=$cityName]' passing c.INFO as "d", CAST (? AS VARCHAR(128)) AS "cityName")~

SELECT companydocs FROM companyinfo WHERE XMLEXISTS('$x/company/emp[@salary > 35000]' PASSING companydocs AS "x")~

CREATE INDEX empindex on companyinfo(companydocs) GENERATE KEY USING XMLPATTERN '//@salary' AS SQL DOUBLE~

CREATE INDEX empindex on companyinfo(companydocs) GENERATE KEY USING XMLPATTERN '/company/emp/@salary' AS SQL DOUBLE~

SELECT companydocs FROM companyinfo WHERE XMLEXISTS('$x/company/emp[@id="31664"]' PASSING companydocs AS "x")~

SELECT companydocs FROM companyinfo WHERE XMLEXISTS('$x/company/emp/dept[@id="K55"] PASSING companydocs AS "x")~

http://blog.csdn.net/dark_agent/archive/2005/06/11/392128.aspx http://www.ibm.com/developerworks/cn/db2/library/techarticles/0304lurie/0304lurie.html http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.ii.doc/opt/tlsset06.htm 对于第二个问题，您可以参考下面的文章 http://www.ibm.com/developerworks/cn/db2/library/techarticles/mag_02q2eaton/eaton.html http://www.ibm.com/developerworks/cn/views/db2/tutorials.jsp?cv_doc_id=85523 DB2 data backup http://oraclecaicai.itpub.net/post/40820/493298

Add generated column SET INTEGRITY FOR SODS2.IBM_EMP OFF alter table SODS2.IBM_EMP add EMAIL_ADR_LCASE varchar(80) GENERATED AS (lower(EMAIL_ADR)) SET INTEGRITY FOR SODS2.IBM_EMP immediate checked FORCE GENERATED DB2中有关日期和时间的函数，及应用

DAYNAME 返回一个大小写混合的字符串，对于参数的日部分，用星期表示这一天的名称（例如，Friday）。 DAYOFWEEK 返回参数中的星期几，用范围在 1-7 的整数值表示，其中 1 代表星期日。 DAYOFWEEK_ISO 返回参数中的星期几，用范围在 1-7 的整数值表示，其中 1 代表星期一。 DAYOFYEAR 返回参数中一年中的第几天，用范围在 1-366 的整数值表示。 DAYS 返回日期的整数表示。 JULIAN_DAY 返回从公元前 4712 年 1 月 1 日（儒略日历的开始日期）到参数中指定日期值之间的天数，用整数值表示。 MIDNIGHT_SECONDS 返回午夜和参数中指定的时间值之间的秒数，用范围在 0 到 86400 之间的整数值表示。 MONTHNAME 对于参数的月部分的月份，返回一个大小写混合的字符串（例如，January）。 TIMESTAMP_ISO 根据日期、时间或时间戳记参数而返回一个时间戳记值。 TIMESTAMP_FORMAT 从已使用字符模板解释的字符串返回时间戳记。 TIMESTAMPDIFF 根据两个时间戳记之间的时差，返回由第一个参数定义的类型表示的估计时差。 TO_CHAR 返回已用字符模板进行格式化的时间戳记的字符表示。TO_CHAR 是 VARCHAR_FORMAT 的同义词。 TO_DATE 从已使用字符模板解释过的字符串返回时间戳记。TO_DATE 是 TIMESTAMP_FORMAT 的同义词。 WEEK 返回参数中一年的第几周，用范围在 1-54 的整数值表示。以星期日作为一周的开始。 WEEK_ISO 返回参数中一年的第几周，用范围在 1-53 的整数值表示。

要使当前时间或当前时间戳记调整到 GMT/CUT，则把当前的时间或时间戳记减去当前时区寄存器： current time - current timezone current timestamp - current timezone

给定了日期、时间或时间戳记，则使用适当的函数可以单独抽取出（如果适用的话）年、月、日、时、分、秒及微秒各部分： YEAR (current timestamp) MONTH (current timestamp) DAY (current timestamp) HOUR (current timestamp) MINUTE (current timestamp) SECOND (current timestamp) MICROSECOND (current timestamp)

因为没有更好的术语，所以您还可以使用英语来执行日期和时间计算： current date + 1 YEAR current date + 3 YEARS + 2 MONTHS + 15 DAYS current time + 5 HOURS - 3 MINUTES + 10 SECONDS

从时间戳记单独抽取出日期和时间也非常简单： DATE (current timestamp) TIME (current timestamp)

而以下示例描述了如何获得微秒部分归零的当前时间戳记：

CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS

如果想将日期或时间值与其它文本相衔接，那么需要先将该值转换成字符串。为此，只要使用 CHAR() 函数：

char(current date) char(current time) char(current date + 12 hours)

要将字符串转换成日期或时间值，可以使用：

TIMESTAMP ('2002-10-20-12.00.00.000000') TIMESTAMP ('2002-10-20 12:00:00') DATE ('2002-10-20') DATE ('10/20/2002') TIME ('12:00:00') TIME ('12.00.00')

TIMESTAMP()、DATE() 和 TIME() 函数接受更多种格式。上面几种格式只是示例，我将把它作为一个练习，让读者自己去发现其它格式。

有时，您需要知道两个时间戳记之间的时差。为此，DB2 提供了一个名为 TIMESTAMPDIFF() 的内置函数。但该函数返回的是近似值，因为它不考虑闰年，而且假设每个月只有 30 天。以下示例描述了如何得到两个日期的近似时差：

timestampdiff (<n>, char( timestamp('2002-11-30-00.00.00')- timestamp('2002-11-08-00.00.00')))

对于 <n>，可以使用以下各值来替代，以指出结果的时间单位：

1 = 秒的小数部分 2 = 秒 4 = 分 8 = 时 16 = 天 32 = 周 64 = 月 128 = 季度 256 = 年 当日期很接近时使用 timestampdiff() 比日期相差很大时精确。如果需要进行更精确的计算，可以使用以下方法来确定时差（按秒计）：

(DAYS(t1) - DAYS(t2)) * 86400 + (MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))

为方便起见，还可以对上面的方法创建 SQL 用户定义的函数：

CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP) RETURNS INT RETURN ( (DAYS(t1) - DAYS(t2)) * 86400 + (MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2)) ) @

如果需要确定给定年份是否是闰年，以下是一个很有用的 SQL 函数，您可以创建它来确定给定年份的天数：

CREATE FUNCTION daysinyear(yr INT) RETURNS INT RETURN (CASE (mod(yr, 400)) WHEN 0 THEN 366 ELSE CASE (mod(yr, 4)) WHEN 0 THEN CASE (mod(yr, 100)) WHEN 0 THEN 365 ELSE 366 END ELSE 365 END END)@

最后，以下是一张用于日期操作的内置函数表。它旨在帮助您快速确定可能满足您要求的函数，但未提供完整的参考。有关这些函数的更多信息，请参考 SQL 参考大全。

SQL 日期和时间函数 DAYNAME 返回一个大小写混合的字符串，对于参数的日部分，用星期表示这一天的名称（例如，Friday）。 DAYOFWEEK 返回参数中的星期几，用范围在 1-7 的整数值表示，其中 1 代表星期日。 DAYOFWEEK_ISO 返回参数中的星期几，用范围在 1-7 的整数值表示，其中 1 代表星期一。 DAYOFYEAR 返回参数中一年中的第几天，用范围在 1-366 的整数值表示。 DAYS 返回日期的整数表示。 JULIAN_DAY 返回从公元前 4712 年 1 月 1 日（儒略日历的开始日期）到参数中指定日期值之间的天数，用整数值表示。 MIDNIGHT_SECONDS 返回午夜和参数中指定的时间值之间的秒数，用范围在 0 到 86400 之间的整数值表示。 MONTHNAME 对于参数的月部分的月份，返回一个大小写混合的字符串（例如，January）。 TIMESTAMP_ISO 根据日期、时间或时间戳记参数而返回一个时间戳记值。 TIMESTAMP_FORMAT 从已使用字符模板解释的字符串返回时间戳记。 TIMESTAMPDIFF 根据两个时间戳记之间的时差，返回由第一个参数定义的类型表示的估计时差。 TO_CHAR 返回已用字符模板进行格式化的时间戳记的字符表示。TO_CHAR 是 VARCHAR_FORMAT 的同义词。 TO_DATE 从已使用字符模板解释过的字符串返回时间戳记。TO_DATE 是 TIMESTAMP_FORMAT 的同义词。 WEEK 返回参数中一年的第几周，用范围在 1-54 的整数值表示。以星期日作为一周的开始。 WEEK_ISO 返回参数中一年的第几周，用范围在 1-53 的整数值表示。

(http://www.fanqiang.com)

SQL0437W Performance of this complex query may be sub-optimal. Reason code: "1". SQLSTATE=01602. Explanation: The statement may achieve sub-optimal performance since the complexity of the query requires resources that are not available or optimization boundary conditions were encountered. The following is a list of reason codes: 1 The join enumeration method was altered due to memory constraints 在Windows环境下使用Cygwin中的gcc编译器实现DB2的嵌入SQL编程 来源： 作者： 出处：巧巧读书 2006-04-09 进入讨论组

Cygwin,gcc的介绍和安装在前面的文章有将过，请参考 在Windows中使用Cygwin环境下的gcc编译器编译Informix ESQ/C程序 对于DB2的嵌入SQL程序，有以下步骤： 1)编写dbconn.sqc程序（一个简单的连接数据库，查询并显示的例子） 2)在Cygwin命令行中键入 db2cmd 启动一个新的db2命令行窗口 在db2命令行窗口中: 3)预编译 db2 prep dbconn.sqc 生成dbconn.c文件 4)编译 gcc -I"C:\Program Files\IBM\SQLLIB\include" dbconn.c "C:\Program Files\IBM\SQLLIB\lib\db2api.lib" 生成a.exe文件 在<在Windows中使用Cygwin环境下的gcc编译器编译Informix ESQ/C程序>文章中提到在使用gcc编译时如果路径中包含空格的路径，就不能正确解析。 这次发现可以将路径用双引号引起来就可以了。 5)执行a.exe，成功 如果需要生成外部绑定文件，(db2执行预编译时默认是做绑定了) 1)db2 prep dbconn.sqc bindfile 生成 dbconn.c, dbconn.bnd 2)手工绑定 db2 bind dbconn.bnd 3)绑定完后一定要重新编译，否则报-818, db2 ? SQL0818 预编译时由预编译器生成的时间戳记与绑定时存在的包内的时间戳记不同 附录1 dbconn.sqc程序 <stdio.h> <string.h> <sql.h> int main() { EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; char firstnme[12+1]; EXEC SQL END DECLARE SECTION; long RetCode = SQL_RC_OK; char ErrorMsg1024; EXEC SQL CONNECT TO sample USER zhangjij USING happyday; EXEC SQL SELECT firstnme INTO :firstnme FROM employee WHERE empno='000099'; if (sqlca.sqlcode != SQL_RC_OK) { RetCode = sqlaintp(ErrorMsg, sizeof(ErrorMsg), 70, &sqlca); switch (RetCode) { case -1: printf("ERROR: Insufficient memory.\n"); break; case -3: printf("ERROR: Message file is inaccessible.\n"); break; case -5: printf("ERROR: Invalid SQLCA, bad buffer, or bad buffer length specified.\n"); break; default: printf("sqlca.sqlcode=[%ld], Message= [%s] \n", sqlca.sqlcode, ErrorMsg); break; } } printf("OK = [%s] \n", firstnme); EXEC SQL DISCONNECT CURRENT; return(0); } Contact

Net search http://www.ibm.com/developerworks/forums/message.jspa?messageID=2798439 db2 select * from SODS2.customer cp where (CONTAINS(cp.cust_name_uprcse, '\"DEMO%\"') = 1) Code page db2set db2codepage=819 db2 create db eddb on C using codeset 1252 territory CN Rebind SP CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'EBIZ1.S_QT_STAT_BY_IBMER', 'ANY') Reset sequence alter table EBIZ1.WEB_QUOTE_AUDIT_HISTORY alter column rec_id restart with 14734 How to start the db2 on windows db2 => reset dbm cfg DB20000I The RESET DATABASE MANAGER CONFIGURATION command completed successfully. db2 => db2start DB20000I The DB2START command completed successfully. db2 => Performance tuning db2advis -d webduke -i sp.sql -t 5 -n WEB_DEV2 -a web_dev2/ebiz02vs http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0002452.htmdb2advisdb2advis SQL procedure tracing WINDOWS下如何知道DB2的端口号? 　　\WINNT\SYSTEM32\DRIVERS\ETC\SERVICES get ref of column select * from syscat.references where REFKEYNAME='WEB_QUOTE_PK' Sametime Using BlueThx ILog http://www.ibm.com/developerworks/websphere/zones/brms/ Notes This issue applies only to Notes on Linux. Notes processes may show high CPU usage; some systems running the Lotus Notes client on Linux may exhibit high CPU usage even when idle. If you experience this issue, it can be alleviated by adding the following line to the "/opt/ibm/lotus/notes/framework/rcp/deploy/jvm.properties" file: vmarg.attach=-Dcom.ibm.tools.attach.enable=no Can you collect logs with the IBM Support Assistant as per: http://w3.ibm.com/connections/wikis/home?lang=en#/wiki/html/Wbf0cf9955 005_4fe2_a8d5_dbb263e58ead/page/Bug%2C%20Problem%2C%20and%20Issue %20Reporting Speed up Just in case you're interested: https://w3.ibm.com/connections/blogs/allerhed/entry/speed_up_your_lotus_notes_performance_by_tuning_the_jvm?lang=en Here's the text from the link: Lotus Notes uses Java Virtual Machine (JVM) for processing of Lotus Notes applications and plugins. IBMs Lotus Notes client configuration has a standard value that defines how many megabytes of RAM Lotus Notes can maximally use for JVM. This value is for PCs with above 1 GB RAM initially set too low, as it should be approx. 1/3 of the RAM of the PC (default value = 256 MB, but should be 768 MB). How to change this: 1.) Shut down Lotus Notes. - To be sure no processes are running in the background, run this command from Start -> Run Type C:\notes\nsd.exe -kill 2.) Find the file "jvm.properties" in this folder (depending on Lotus Notes version): In Lotus Notes version 8.5.x: C:\Notes\framework\rcp\deploy\ 3.) Open the jvm.properties files in a texteditor like notepad. 4.) You will now see a text surrounded by a lot of pound signs #### The first 'property' after the last # sign is: vmarg.Xmx=-Xmx256m This is where you must change 256 MB RAM to 768 RAM (if you have 2GB RAM installed which is IBM standard) vmarg.Xmx=-Xmx768m 5.) Save your changes and close the file. Now start Lotus Notes and hopefully you will find it a lot faster already at password prompt and afterwards for opening workspace, mailbox etc. Build forge http port 8083 shutdown 8086 key store password : btsjazz db2 create database bf3 on datad/bfdatabase pagesize 16384

http://btsjazz.dyn.webahead.ibm.com/ user name/password: root/btsjazz changwei/btsjazz lic server rl01.rchland.ibm.com:27000 rpm -iUvh /datad/eadftp/BuildForge7.1.1/bf711ag.zip_FILES/rhel5-bfagent-7.1.1.0-0-0022.rpm bfagent -s Start build forge in Linux in rc.local file: su -c "/opt/buildforge/rc/buildforge start" # start the bf server su -c "bfagent -s" # start the bf agent How can I update my new database password in IBM Rational Build Forge? Rational Build Forge utilizes two separate copies of the buildforge.conf file. If the password for accessing the database has changed, you must update both configuration files for Rational Build Forge to continue to function. The default locations for the buildforge.conf files are: Microsoft Windows: C:\Program Files\IBM\Build Forge and C:\Program Files\IBM\Build Forge\Apache\tomcat\webapps\rbf-services\WEB-INF\classes UNIX / Linux: /opt/buildforge/Platform and /opt/buildforge/server/tomcat/webapps/rbf-services/WEB-INF/classes As a best practice, back up your buildforge.conf files before making changes. The existing db_password in the buildforge.conf files will be encrypted. You can store a plain-text password in this file and the Rational Build Forge process will re-write the file with the updated encrypted version when the engine is re-started with the new information. If you prefer to store the password in the encrypted form right away, you can use the "bfpwencrypt" program, which is included with your Rational Build Forge install: Microsoft Windows: C:\Program Files\IBM\Build Forge\bfpwencrypt -e <new-password> UNIX / Linux: /opt/buildforge/Platform/bfpwencrypt -e <new-password> Take the output of that command and store it in each buildforge.conf file, replacing the encrypted text on the "db_password" line: db_password 5918axxxxxxxxadc168e34e63aca6d29caeec965c23

Setup WAS profile admin console user name and password: admin/pass Profile name: RQM3, http: 9083; https: 9446 https://itrqmweb.cn.ibm.com:9444/help/index.jsp?topic=/com.ibm.rational.test.qm.doc/topics/c_qm_overview.html Problem: If you are using Repotools to create Rational? Quality Manager tables using DB2? as your database, the error message SQL Message: DB2 SQL error: SQLCODE: -286, SQLSTATE: 42727, SQLERRMC: 16384;DB2ADMIN could display. Solution: When you create the Rational Quality Manager database, you must increase the default bufferpool and table page size space to 16k. For more information, see the Rational Quality Manager installation documentation. http://publib.boulder.ibm.com/infocenter/wsdoc400/v6r0/index.jsp?topic=/com.ibm.websphere.iseries.doc/info/ae/ae/twsu_create_db2.html https://www-304.ibm.com/support/docview.wss?uid=swg21391223 create database RQM automatic storage yes on /datad/database/RQM using codeset UTF-8 territory US COLLATE USING SYSTEM pagesize 16384 CREATE BUFFERPOOL "RQMbf" SIZE 250 PAGESIZE 16k create regular tablespace RQMtabspace pagesize 16K MANAGED BY AUTOMATIC STORAGE extentsize 64 prefetchsize 32 bufferpool RQMbf db2 UPDATE DATABASE CONFIGURATION FOR RQM USING APP_CTL_HEAP_SZ 256 db2stop force db2start Tomcat If you plan to use an LDAP registry with your Jazz Team Server, you must configure your Apache Tomcat or WebSphere Application Server to use an LDAP registry to authenticate users. LDAP configuration setup process To configure LDAP connection and import users follow these steps: Stop the server. If you have previously tried to install your LDAP server, backup the JazzInstallDir/jazz/server/tomcat/conf/server.xml file. Open the JazzInstallDir/jazz/server/tomcat/conf/server.xml file for editing and uncomment the following realm tag: <Realm className="org.apache.catalina.realm.UserDatabaseRealm" resourceName="UserDatabase" digest="SHA-1" digestEncoding="UTF-8"/> Save the file and restart the server. Open a Web browser window and go to https://localhost:9443/jazz/setup. Login with ADMIN/ADMIN (both username and password are case sensitive). Click the Custom Setup button. click Next until you reach page 5, Setup User Registry. In the Step 1 section under Type, select the Tomcat User Database radio button. 10. Under Step 3, select the checkbox for Disable default ADMIN access. 11. Under Step 4, Select the checkbox for RQM - xxx license. 12. Click Next to create this first user. 13. Click Previous to go back to page 5, Setup User Registry. 14. Under Step 1, select the LDAP radio button. 15. Fill out the fields in Section 2. Note: I have created following blue groups - DSW_CN_TESTING_ADMIN, DSW_CN_TESTING, DSW_CN_TESTING_DWADMIN, DSW_CN_TESTING_GUEST, so you do not need create them by yourself ** LDAP Registry Location: ldap://bluepages.ibm.com:389 Base User DN: c=cn,ou=bluepages,o=ibm.com User Property Names Mapping:userId=mail,name=callupname,emailAddress=mail Base Group DN:ou=memberlist,ou=ibmgroups,o=ibm.com Jazz to LDAP Group Mapping: JazzAdmins=DSW_CN_TESTING_ADMIN,JazzUsers=DSW_CN_TESTING,JazzDWAdmins=DSW_CN_TESTING_DWADMIN,JazzGuests=DSW_CN_TESTING_GUEST Group Name Property: cn Group Member Property: uniqueMember ** Shut down the server. Configure the Web container for LDAP in Apache Tomcat. ** To configure the Web container for LDAP in Apache Tomcat follow these steps: 1) Open the JazzInstallDir/jazz/server/tomcat/conf/server.xml file for editing and comment the following tag: <Realm className="org.apache.catalina.realm.UserDatabaseRealm" resourceName="UserDatabase" digest="SHA-1" digestEncoding="UTF-8"/> 2) Add below content <Realm className="org.apache.catalina.realm.JNDIRealm" debug="9" connectionURL="ldap://bluepages.ibm.com:389" userBase="ou=bluepages,o=ibm.com" userSearch="(mail={0})" userSubtree="true" roleBase="ou=memberlist,ou=ibmgroups,o=ibm.com" roleSubtree="false" roleSearch="(uniquemember={0})" roleName="cn"/> 3) Open the JazzInstallDir/jazz/server/tomcat/webapps/jazz/WEB-INF/web.xml file for editing and link and map the security role references to the security roles: <web-app id="WebApp"> <servlet id="bridge"> <servlet-name>equinoxbridgeservlet</servlet-name> <display-name>Equinox Bridge Servlet</display-name> Equinox Bridge Servlet <servlet-class>org.eclipse.equinox.servletbridge.BridgeServlet</servlet-class> <init-param><!— ... —></init-param> <!— ... —> <load-on-startup>1</load-on-startup> <!— Add the following section if the LDAP group names are different from Jazz group names —> <security-role-ref> <role-name>JazzAdmins</role-name> <role-link>DSW_CN_TESTING_ADMIN</role-link> </security-role-ref> <security-role-ref> <role-name>JazzDWAdmins</role-name> <role-link>DSW_CN_TESTING_DWADMIN</role-link> </security-role-ref> <security-role-ref> <role-name>JazzGuests</role-name> <role-link>DSW_CN_TESTING_GUEST</role-link> </security-role-ref> <security-role-ref> <role-name>JazzUsers</role-name> <role-link>DSW_CN_TESTING</role-link> </security-role-ref> <!— End Addition —> </servlet> ............. .............. <auth-constraint> <role-name>JazzUsers</role-name> <role-name>JazzAdmins</role-name> <role-name>JazzGuests</role-name> <role-name>JazzDWAdmins</role-name> <role-name>DSW_CN_TESTING</role-name> <role-name>DSW_CN_TESTING_ADMIN</role-name> <role-name>DSW_CN_TESTING_GUEST</role-name> <role-name>DSW_CN_TESTING_DWADMIN</role-name> </auth-constraint> .............. <security-constraint> <web-resource-collection> <web-resource-name>adminsecure</web-resource-name> <url-pattern>/admin/cmd/*</url-pattern> </web-resource-collection> <auth-constraint> <role-name>JazzAdmins</role-name> <role-name>DSW_CN_TESTING_ADMIN</role-name> </auth-constraint> <user-data-constraint> <transport-guarantee>CONFIDENTIAL</transport-guarantee> </user-data-constraint> </security-constraint> ........... <login-config> <auth-method>FORM</auth-method> <form-login-config> <form-login-page>/auth/authrequired</form-login-page> <form-error-page>/auth/authfailed</form-error-page> </form-login-config> </login-config> <security-role> <role-name>JazzAdmins</role-name> </security-role> <security-role> <role-name>JazzDWAdmins</role-name> </security-role> <security-role> <role-name>JazzUsers</role-name> </security-role> <security-role> <role-name>JazzGuests</role-name> </security-role> <security-role> <role-name>DSW_CN_TESTING_ADMIN</role-name> </security-role> <security-role> <role-name>DSW_CN_TESTING_DWADMIN</role-name> </security-role> <security-role> <role-name>DSW_CN_TESTING</role-name> </security-role> <security-role> <role-name>DSW_CN_TESTING_GUEST</role-name> </security-role> </web-app> ** Restart the server. Open a Web browser window and go to https://localhost:9443/jazz/admin. Login with the user ID that you created to test your connection. RSA 没有修改的文件也显示星号 解决办法是升级内核。 http://kernel.ubuntu.com/~kernel-ppa/mainline/ RTC 301 opt/IBM/RTC/CLM_3.0.1_Build/buildsystem/buildengine/eclipse/jbe.sh -createPasswordFile /net/rpo-fs015/vol/homes/cvtbld/changweipw4rtc301.txt /opt/IBM/java/java_1.6/current/sdk/jre/bin/java -jar /opt/IBM/RTC/CLM_3.0.1_Build/buildsystem/buildengine/eclipse/plugins/org.eclipse.equinox.launcher_1.0.201.R35x_v20090715.jar -repository https://9.32.152.213/jazz -userid changwei@cn.ibm.com -passwordFile /net/rpo-fs015/vol/homes/cvtbld/changweipw4rtc301.txt -engineId CVT-DSW-SQO

WSVR0501E: 创建组件 com.ibm.ws.cluster.runtime.ProcessRuntimeImpl 时出错 java.lang.NoClassDefFoundError: com.ibm.websphere.cluster.topography.DescriptionManagerFactory (initialization failure) at java.lang.J9VMInternals.initialize(J9VMInternals.java:140) at com.ibm.ws.cluster.runtime.ProcessRuntimeImpl.initialize(ProcessRuntimeImpl.java:369) at com.ibm.ws.runtime.component.ContainerHelper.initWsComponent(ContainerHelper.java:1191) at com.ibm.ws.runtime.component.ContainerHelper.initializeComponent(ContainerHelper.java:1098) at com.ibm.ws.runtime.component.ContainerHelper.initializeComponents(ContainerHelper.java:900) at com.ibm.ws.runtime.component.ContainerImpl.initializeComponents(ContainerImpl.java:776) at com.ibm.ws.runtime.component.ContainerImpl.initializeComponents(ContainerImpl.java:750) at com.ibm.ws.runtime.component.ServerImpl.initialize(ServerImpl.java:332) at com.ibm.ws.runtime.WsServerImpl.bootServerContainer(WsServerImpl.java:280) at com.ibm.ws.runtime.WsServerImpl.start(WsServerImpl.java:214) at com.ibm.ws.runtime.WsServerImpl.main(WsServerImpl.java:666) at com.ibm.ws.runtime.WsServer.main(WsServer.java:59) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:48) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:600) at com.ibm.wsspi.bootstrap.WSLauncher.launchMain(WSLauncher.java:213) at com.ibm.wsspi.bootstrap.WSLauncher.main(WSLauncher.java:93) at com.ibm.wsspi.bootstrap.WSLauncher.run(WSLauncher.java:74) at org.eclipse.core.internal.runtime.PlatformActivator$1.run(PlatformActivator.java:78) at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:92) at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:68) at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:400) at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:177) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:48) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:600) at org.eclipse.core.launcher.Main.invokeFramework(Main.java:340) at org.eclipse.core.launcher.Main.basicRun(Main.java:282) at org.eclipse.core.launcher.Main.run(Main.java:981) at com.ibm.wsspi.bootstrap.WSPreLauncher.launchEclipse(WSPreLauncher.java:341) at com.ibm.wsspi.bootstrap.WSPreLauncher.main(WSPreLauncher.java:111) Caused by: java.lang.IllegalStateException: java.lang.NullPointerException at com.ibm.ws.util.ImplFactory.loadImplFromClass(ImplFactory.java:354) at com.ibm.ws.util.ImplFactory.loadImplFromKey(ImplFactory.java:328) at com.ibm.ws.util.ImplFactory.loadImplFromKey(ImplFactory.java:332) at com.ibm.ws.wlm.Factory$4.run(Factory.java:141) at java.security.AccessController.doPrivileged(AccessController.java:251) at com.ibm.ws.wlm.Factory.loadImpl(Factory.java:139) at com.ibm.websphere.cluster.topography.DescriptionManagerFactory.<clinit>(DescriptionManagerFactory.java:50) at java.lang.J9VMInternals.initializeImpl(Native Method) at java.lang.J9VMInternals.initialize(J9VMInternals.java:200) at com.ibm.ws.cluster.selection.AdvisorMediatorA.<clinit>(AdvisorMediatorA.java:60) at java.lang.J9VMInternals.initializeImpl(Native Method) at java.lang.J9VMInternals.initialize(J9VMInternals.java:200) at java.lang.J9VMInternals.initialize(J9VMInternals.java:167) at java.lang.J9VMInternals.newInstanceImpl(Native Method) at java.lang.Class.newInstance(Class.java:1345) at com.ibm.ws.util.ImplFactory.loadImplFromClass(ImplFactory.java:349) at com.ibm.ws.util.ImplFactory.loadImplFromKey(ImplFactory.java:328) at com.ibm.ws.util.ImplFactory.loadImplFromKey(ImplFactory.java:332) at com.ibm.ws.wlm.Factory$4.run(Factory.java:141) at java.security.AccessController.doPrivileged(AccessController.java:251) at com.ibm.ws.wlm.Factory.loadImpl(Factory.java:139) at com.ibm.wsspi.cluster.monitor.AdvisorFactory.<clinit>(AdvisorFactory.java:47) at java.lang.J9VMInternals.initializeImpl(Native Method) at java.lang.J9VMInternals.initialize(J9VMInternals.java:200) at com.ibm.ws.cluster.runtime.WLMDiagnosticModule.ffdcDumpDefaultAdvisorMediator(WLMDiagnosticModule.java:367) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:48) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:600) at com.ibm.ws.ffdc.DiagnosticModule.getDataForDirective(DiagnosticModule.java:305) at com.ibm.ws.ffdc.DiagnosticModule.getDataForDirectives(DiagnosticModule.java:279) at com.ibm.ws.ffdc.DiagnosticModule.dumpComponentData(DiagnosticModule.java:144) at com.ibm.ws.ffdc.impl.DMAdapter.processDM(DMAdapter.java:123) at com.ibm.ws.ffdc.impl.DMAdapter.formatTo(DMAdapter.java:114) at com.ibm.ffdc.util.provider.IncidentLogger.writeIncidentTo(IncidentLogger.java:63) at com.ibm.ws.ffdc.impl.FfdcProvider.logIncident(FfdcProvider.java:206) at com.ibm.ws.ffdc.impl.FfdcProvider.logIncident(FfdcProvider.java:135) at com.ibm.ffdc.util.provider.FfdcProvider.log(FfdcProvider.java:259) at com.ibm.ws.ffdc.impl.FfdcProvider.log(FfdcProvider.java:148) at com.ibm.ffdc.util.provider.IncidentEntry.log(IncidentEntry.java:105) at com.ibm.ffdc.util.provider.Ffdc.log(Ffdc.java:90) at com.ibm.ws.ffdc.FFDCFilter.processException(FFDCFilter.java:114) at com.ibm.ws.cluster.ProcessProperties.<init>(ProcessProperties.java:287) at com.ibm.ws.cluster.ProcessProperties.<clinit>(ProcessProperties.java:240) at java.lang.J9VMInternals.initializeImpl(Native Method) at java.lang.J9VMInternals.initialize(J9VMInternals.java:200) at com.ibm.ws.cluster.runtime.ProcessRuntimeImpl.initialize(ProcessRuntimeImpl.java:310) ... 31 more Caused by: java.lang.NullPointerException at com.ibm.ws.cluster.WLMCustomPropertyUtility.getBBCallbacksEnableWLMThreads(WLMCustomPropertyUtility.java:528) at com.ibm.ws.cluster.propagation.bulletinboard.BBDescriptionManager.<init>(BBDescriptionManager.java:166) at java.lang.J9VMInternals.newInstanceImpl(Native Method) at java.lang.Class.newInstance(Class.java:1345) at com.ibm.ws.util.ImplFactory.loadImplFromClass(ImplFactory.java:349) ... 77 more

I will cover the following features to demonstrate using IBM data studio to develop db2 stored procedure.

---- Now let's begin the demo. Open IBM data studio. Data studio is an eclipse plug-in.

http://download.boulder.ibm.com/ibmdl/pub/software/websphere/wasce/updates/

• Installed all the feature when we install the IBM data studio.
• Go to bin\IBM\SDP70\dsdev\bin, click wasce_setup-1.1.exe to setup wasce. default user/password: system/manager

----