progress数据库的配置与运行
progress数据库的配置与运行
1. 环境变量设置:
在shell为tcsh的情况, 修改.tcshrc , 加入:
setenv DLC /home/progress/dlc
setenv WRKDIR /home/progress/wrk
setenv PROEXE $DLC/bin/_progres
setenv PROMSGS $DLC/promsgs
setenv PATH "……:$DLC/bin"
2. Progress数据库的启动:
progress安装后就已经有demo, sports,isports,sports2000,empty几个初始的数据库.
[root@xfs dlc]# pwd
/home/progress/dlc
[root@xfs dlc]# proserve sports
PROGRESS PATCH Version 9.1C09 as of February 26, 2002
13:54:25 BROKER : You are not allowed to connect to a database in $DLC. (1379)
13:54:25 BROKER : ** This process terminated with exit code 2. (8619)
以上信息表明我们不能在环境变量$DLC指定的路径下直接执行proserve命令初始化和sports数据库的连接.
[root@xfs dlc]# pwd
/root/sports
[root@xfs ~/sports]# ls /home/progress/dlc/sports.*
/home/progress/dlc/sports.b1 /home/progress/dlc/sports.db
/home/progress/dlc/sports.d1 /home/progress/dlc/sports.pl
[root@xfs ~/sports]# ls /home/progress/dlc/sports_*
/home/progress/dlc/sports_7.d1 /home/progress/dlc/sports_8.d1
[root@xfs ~/sports]# cp /home/progress/dlc/sports.* ./
[root@xfs ~/sports]# cp /home/progress/dlc/sports_* ./
[root@xfs ~/sports]# ls
sports_7.d1 sports_8.d1 sports.b1 sports.d1 sports.db sports.pl
sports.*和sports_*等文件是sports数据库存储在磁盘上的所有必须的文件, 我们把它们一起copy到/root/sports/下面, 就等于将sports数据库复制一份到/root/sports下面了.
[root@xfs ~/sports]# proserve sports -S 2000 -N tcp
PROGRESS PATCH Version 9.1C09 as of February 26, 2002
18:00:01 BROKER 0: Multi-user session begin. (333)
18:00:01 BROKER 0: Begin Physical Redo Phase at 0 . (5326)
18:00:01 BROKER 0: Physical Redo Phase Completed at blk 0 off 167 upd 0. (7161)
18:00:01 BROKER 0: Started for 2000 using tcp, pid 26815. (5644)
-S 2000表示在端口2000上提供sports数据库的连接服务, -N tcp表示以tcp socket提供连接(或者选择udp), 如果不加上-S 2000, 则proserve sports虽然没有出错信息, 但并未能初始化sports数据库的连接服务.
启动sports数据库的连接服务后,
[root@xfs ~/sports]# netstat -a|grep 2000
tcp 0 0 *:2000 *:* LISTEN
[root@xfs ~/sports]# ls
sports_7.d1 sports.b1 sports.db sports.lic sports.pl
sports_8.d1 sports.d1 sports.lg sports.lk
可以看到多了sports.lg , sports.lic, sports.lk 3个文件.
[root@xfs ~/sports]# ps -ef | grep progress
root 25601 1 0 13:32 pts/0 00:00:00 /home/progress/dlc/bin/_mprosrv
_mprosrv为监控和提供数据库连接服务的进程.
3. SQLExplorer: 类似Oracle sql plus的工具
[root@xfs ~/sports]# sqlexp -H localhost -S 2000 -db sports
PROGRESS PATCH Version 9.1C09 as of February 26, 2002
Connecting user "root" to URL "jdbc:jdbcprogress:T:localhost:2000:sports"... (8920)
SQLExplorer>
这样就进入了SQLExplorer的命令行界面, 可在其下执行SQL语句进行表的创建和管理等. 在本机上执行的情况–H localhost可以省去, 但-S 2000不能省, 必须指定为上面proserve sports的时侯所指定的端口, 才能正确连接到sports数据库上去.
SQLExplorer命令行下输入SQL语句和命令时是大小写无关的, 但必须以”; “显式表明一个SQL语句的结束.
Progress数据库安装好以后就有一个并且只有一个唯一的DBA, 其用户名为sysprogress, 并且以其他用户名调用sqlexp进入SQLExplorer的时侯都不具备创建或修改任何表和数据的权限. 上面sqlexp -H localhost -S 2000 -db sports 没有加-user 选项, 则SQLExplorer就认为使用sports数据库的用户名为unix用户即root, 我们接着执行create table命令的话可以看到用户root是不具备创建table的权限的:
SQLExplorer>create table test(
1> name varchar(20),
2> age integer
3> );
=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-20057
[JDBC Progress Driver]:No RESOURCE privileges (7574)
所以我们必须首先以sysprogress身分登入SQLExplorer, 然后grant权限给用户root:
[root@xfs ~/sports]# sqlexp -H localhost -S 2000 -user sysprogress -db sports
PROGRESS PATCH Version 9.1C09 as of February 26, 2002
Connecting user "sysprogress" to URL "jdbc:jdbcprogress:T:localhost:2000:sports"... (8920)
QLExplorer>create table test(
1> name varchar(20),
2> age integer
3> );
=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-211015
[JDBC Progress Driver]:Database object (table, view, index, trigger,procedure, or synonym) owned by "sysprogress" cannot be created, dropped, or altered. (7883) //sysprogress本身拥有的表,视图等是不可修改的, 因为sysprogress DBA相当于unix的超级用户, 为数据库安全起见不能对其修改.
查看系统中所有的表: (相当于mysql的 show tables;命令)
SQLExplorer>select * from sysprogress.systables;
ID TBL CREATOR OWNER TBLTYPE SEGID HAS_PCNSTRS HAS_FCNSTRS HAS_CCNSTRS HAS_UCNSTRS TBL_STATUS RSSID
----------- -------------------------------- -------------------------------- -------------------------------- ------- ----------- ----------- ----------- ----------- ----------- ---------- -----------
2 Customer PUB PUB T 0 N N N N Y 5
1 Invoice PUB PUB T 0 N N N N Y 5
3 Item PUB PUB T 0 N N N N Y 5
8 Local-Default PUB PUB T 0 N N N N Y 5
4 Order PUB PUB T 0 N N N N Y 5
5 Order-Line PUB PUB T 0 N N N N Y 5
9 Ref-Call PUB PUB T 0 N N N N Y 5
6 Salesrep PUB PUB T 0 N N N N Y 5
7 State PUB PUB T 0 N N N N Y 5
32786 SYSATTACHTBLS SYSPROGRESS SYSPROGRESS V 0 N N N N 5
32807 SYSBIGINTSTAT SYSPROGRESS SYSPROGRESS V 0 N
.....................................................
查看某个表的字段结构:
SQLExplorer>select * from sysprogress.syscolumns where tbl='test';
ID COL TBL OWNER COLTYPE WIDTH SCALE NULLFLAG DFLT_VALUE CHARSET COLLATION
----------- -------------------------------- -------------------------------- -------------------------------- ---------- ----------- ----------- -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------- --------------------------------
3 AGE TEST ROOT integer 4 0 Y iso8859-1 basic_S
2 NAME TEST ROOT varchar 20 0 Y iso8859-1 basic_S
SQLExplorer>grant resource to root; //赋予权限给用户root
SQLExplorer>grant dba to root;
SQLExplorer>commit;
再用root身分登入:
[root@xfs ~/sports]# sqlexp -H localhost -S 2000 -db sports
PROGRESS PATCH Version 9.1C09 as of February 26, 2002
Connecting user "root" to URL "jdbc:jdbcprogress:T:localhost:2000:sports"... (8920)
SQLExplorer>create table test(
1> name varchar(20),
2> age integer
3> );
SQLExplorer>select * from sysprogress.systables;
......
200 TEST ROOT ROOT T 0 N
SQLExplorer>select * from test;
NAME AGE
-------------------- -----------
可以看到用户root现在有了权限创建表和修改数据.
其他命令:
查看帮助:
SQLExplorer>@help;
Available options include:
@AutoCommit { true | false }
@ColumnWidthLimit { 1...31995 }
@ColumnWidthMinimum { 1...2048 }
@ConnectTimeout { 1...300 }
@Connect jdbc:jdbcprogress:T:localhost:<port>:<database>
- or -
@Connect jdbc:odbc:<data-source-name>
@DisableWarnings { true | false }
@Disconnect { true | false }
@EchoAll { true | false }
@EchoCmd { true | false }
@EchoComments { true | false }
@EchoSql { true | false }
@FetchLimit { 1...32767 }
@HasColumnLimit { true | false }
@HasFetchLimit { true | false }
@Help [ <option> [ <option>...]]
@Logfile <logFilename>
@Logging { true | false }
@PageLimit { 1...32767 }
@Pager { true | false }
@ReportFormat { Standard | By Label }
@Reset [ <option> [ <option>...]]
@Run <runFilename>
@Show [ <option> [ <option>...]]
@SqlVerbose { true | false }
@TransactionIsolation { Read Uncommitted | Read Committed | Repeatable Read | Serializable }
- or -
@TransactionIsolation { 0 | 1 | 2 | 3 };
Ok: help. (8932)
SQLExplorer>@show;
Options values are:
AutoCommit false
ColumnWidthLimit 25
ColumnWidthMinimum 1
ConnectTimeout 180
Connect jdbc:jdbcprogress:T:localhost:2000:sports
DisableWarnings true
Disconnect true
EchoAll false
EchoCmd false
EchoComments false
EchoSql false
FetchLimit 101
HasColumnLimit false
HasFetchLimit true
Logfile /root/sports/SQLExplorerSession.log
Logging false
PageLimit 22
Pager false
ReportFormat Standard
SqlVerbose false
TransactionIsolation 1: Read Committed;
Ok: show. (8932)
4. 关闭一个数据库的连接服务
[root@xfs ~/sports]# proshut sports
1 Disconnect a User
2 Unconditional Shutdown
3 Emergency Shutdown (Kill All)
x Exit
Enter choice> 2
Shutdown is executing. (1613)
Shutdown complete. (1614)
数据库连接服务关闭后我们便无法再用SQLExplorer去连接和访问它:
[root@xfs ~/sports]# sqlexp -H localhost -S 2000 -db sports
PROGRESS PATCH Version 9.1C09 as of February 26, 2002
Connecting user "root" to URL "jdbc:jdbcprogress:T:localhost:2000:sports"... (8920)
error in tcp bind 111
Error: [JDBC Progress Driver]:Error in Network Daemon. (8933)
See "/root/sports/sqlexp.log" and "/root/sports/SQLExplorer.exceptions" files for more information. (8950)
5. 用JDBC访问Progress数据库的demo
demo程序在$DLC/src/samples/sql92/下面的JdbcProgressTest.java, 首先要在.tcshrc里加入设置CLASSPATH: setenv CLASSPATH ".:/home/progress/dlc/java/jdbc.zip", 然后javac编译成JdbcProgressTest.class, 然后用java JdbcProgressTest [jdbcURL] [username ] [password]的格式调用, 例如java JdbcProgressTest jdbc:JdbcProgress:T:localhost:2000:testdb root.
如果想在Windows下面运行JDBC程序访问数据库, 则必须到http://www.progress.com/patches/index.htm里的下面link中:
WIN NT 32 Intel 9.1C19 9.1C 2002/03/08
再进入download 91C19.exe的页面, 把91C19.exe download下来.
运行91C19.exe, 一般缺省解压到c:\program files\progress目录,解压完毕后, 必须在windows的环境变量里加入一项DLC, 值为前面解压到的目录即c:\program files\progress, 在环境变量PATH的值中添加入%DLC%\bin; 使其成为PATH=”….;%DLC%\bin;”, 然后还需要在CLASSPATH中加入c:\program files\progress\java\progress.zip, 完成这些环境变量设置后再运行java JdbcProgressTest jdbc:JdbcProgress:T:192.168.1.3:2000:cms root, 可以看到已经能在Windows下运行JDBC程序访问另一台机器或本机上的Progress数据库了.
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ljah/archive/2007/10/22/1836576.aspx
